﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CMISLibrary.Entities;
using CMISLibrary;
using BCNPCObject.DB;


namespace BCNPCObject
{
    public class cls_TRUY_THU_Controller
    {
        public DataSet Get_thongtin_khach_hang(string strMa_dviqly, string strMa_ddo)
        {
            try
            {
                var DDO = (from p in CMIS2.DB.HDG_DIEM_DO
                          where p.MA_DVIQLY == strMa_dviqly
                          && p.MA_DDO == strMa_ddo
                          select p).ToList();

                var DDOMaxHL = DDO.Where(c => c.NGAY_HLUC == DDO.Where(c1 => c1.MA_DDO == c.MA_DDO).Max(c2 => c2.NGAY_HLUC));

                string[] ArrMa_khang = DDOMaxHL.Select(c=> c.MA_KHANG).Distinct().ToArray();

                var KHACHHANG = (from p in CMIS2.DB.HDG_KHACH_HANG
                                where ArrMa_khang.Contains(p.MA_KHANG)
                                 select p).ToList();
                var KHACHHANGMaxHL = KHACHHANG.Where(c => c.NGAY_HLUC == KHACHHANG.Where(c1 => c1.MA_KHANG == c.MA_KHANG).Max(c2 => c2.NGAY_HLUC));

                long[] ArrMa_hdong = KHACHHANGMaxHL.Select(c => c.ID_HDONG).Distinct().ToArray();

                long IDHDONG = ArrMa_hdong[0];

                var HOPDONG = from p in CMIS2.DB.HDG_HOP_DONG
                              where p.ID_HDONG == IDHDONG
                              select new 
                              {
                                  p.MA_DVIQLY,
                                  p.ID_HDONG,
                                  p.MA_HDONG,
                                  p.MA_YCAU_KNAI,
                                  p.MA_LOAIHD,
                                  p.NGAYKY_HDONG,
                                  p.NGAY_HLUC,
                                  p.TRANG_THAI,
                                  p.HLUC_TUNGAY,
                                  p.HLUC_DENNGAY,
                                  p.MA_LDO_TLY,
                                  p.THANG,
                                  p.NAM,
                                  p.SONAM_HLUC,
                                  p.STIEN_DCOC,
                                  p.KWH_TDUONG,
                                  p.TGIAN_TDUONG,
                                  p.LOAI_TIEN,
                                  p.DIEN_AP,
                                  p.TBAO_SUCO,
                                  p.DAP_DDONG,
                                  p.TAN_SO,
                                  p.TANSO_SAISO,
                                  p.NGAY_GCS,
                                  p.SOLAN_TTOAN,
                                  p.THOIHAN_TTOAN,
                                  p.SNAM_GHAN,
                                  p.DCHI_TTOAN
                              };

                var joi1 = from p in DDOMaxHL.ToList()
                          join m in KHACHHANGMaxHL.ToList()
                          on new { p.MA_KHANG } equals new { m.MA_KHANG }
                          select new 
                          {
                              p.MA_DDO,
                              p.ID_HDONG,
                              p.MA_KHANG,
                              m.TEN_KHANG,
                              DIA_CHI = (m.SO_NHA == null ? "" : m.SO_NHA.ToString()) + (m.DUONG_PHO == null ? "" : m.DUONG_PHO.ToString())
                          };

                var joi = from p in joi1.ToList()
                          join m in HOPDONG.ToList()
                          on new { p.ID_HDONG } equals new { m.ID_HDONG }
                          select new 
                          {
                              p.MA_DDO,
                              p.ID_HDONG,
                              p.MA_KHANG,
                              p.TEN_KHANG,
                              p.DIA_CHI,
                              m.DCHI_TTOAN
                          };

                if (joi != null && joi.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(joi);
                    dt.TableName = "THONGTIN_KHACHHANG";
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }

                return null;

            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_thongtin_Ban_dien(string strMa_dviqly)
        {
            try
            {
                var QLY = from p in CMIS2.DB.D_DVI_QLY
                          where p.MA_DVIQLY == strMa_dviqly
                          select new 
                          {
                              p.MA_DVIQLY,
                              p.TEN_DVIQLY,
                              p.MA_DVICTREN,
                              p.CAP_DVI,
                              p.DIA_CHI,
                              p.ID_DIA_CHINH,
                              p.DIEN_THOAI,
                              p.DTHOAI_KDOANH,
                              p.DTHOAI_NONG,
                              p.DTHOAI_TRUC,
                              p.FAX,
                              p.EMAIL,
                              p.MA_STHUE,
                              p.DAI_DIEN,
                              p.CHUC_VU,
                              p.SO_UQUYEN,
                              p.NGAY_UQUYEN,
                              p.TEN_DVIUQ,
                              p.DCHI_DVIUQ,
                              p.CVU_UQUYEN,
                              p.TNGUOI_UQUYEN                             
                          };

                if (QLY != null && QLY.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(QLY);
                    dt.TableName = "THONGTIN_BANDIEN";
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }

                return null;


            }
            catch
            { return null; }
        }

        public string Get_loai_khang(string strMa_dviqly, string strMa_ddo)
        {
            try
            {
                string Loai_khang = "";
                var APGIA = (from p in CMIS2.DB.HDG_BBAN_APGIA
                           where p.MA_DVIQLY == strMa_dviqly
                           && p.MA_DDO == strMa_ddo
                           select p).ToList();

                var APGIAMaxHL = APGIA.Where(c => c.NGAY_HLUC == APGIA.Where(c1 => c1.MA_DDO == c.MA_DDO).Max(c2 => c2.NGAY_HLUC));

                // công tơ cơ
                var DDOCO = from p in APGIAMaxHL
                           where p.LOAI_BCS == "KT"
                           select p;

                if (DDOCO != null && DDOCO.Count() > 0)
                {

                    var SH = from p in DDOCO
                             where p.MA_NHOMNN.Contains("SH")
                             select p;

                    if (SH != null && SH.Count() > 0)
                    {
                        Loai_khang = "10"; // sinh hoạt có bậc thang
                    }
                    else
                    {
                        Loai_khang = "11";// cong to co - không có bậc thang
                    }
                }
                else
                {
                    Loai_khang = "20";
                }



                return Loai_khang;

            }
            catch
            {
                return "20"; // công tơ điện tử
            }
        }

        public DataSet Get_Apgia_SHBT(string strMa_dviqly, string strMa_ddo)
        {
            try
            {
                var APGIA = (from p in CMIS2.DB.HDG_BBAN_APGIA
                             where p.MA_DVIQLY == strMa_dviqly
                             && p.MA_DDO == strMa_ddo
                             select p).ToList();

                var APGIAMaxHL = APGIA.Where(c => c.NGAY_HLUC == APGIA.Where(c1 => c1.MA_DDO == c.MA_DDO).Max(c2 => c2.NGAY_HLUC));

                var DGiaNhomNNSH = (from p in CMIS2.DB.D_GIA_NHOMNN
                                   where p.MA_NHOMNN.Contains("SH")
                                   select p).ToList();

                var DGiaNhomNNSHMaxHL = DGiaNhomNNSH.Where(c => c.NGAY_ADUNG == DGiaNhomNNSH.Where(c1 => c1.MA_NHOMNN == c.MA_NHOMNN && c1.MA_NGIA == c.MA_NGIA).Max(c2 => c2.NGAY_ADUNG));

                var DGiaNhomNN = from p in DGiaNhomNNSHMaxHL
                                 join m in APGIAMaxHL
                                 on new { p.MA_NHOMNN, p.MA_NGIA }
                                 equals new { m.MA_NHOMNN, m.MA_NGIA }
                                 select p;

                if(DGiaNhomNN != null && DGiaNhomNN.Count()>0)
                {
                    DataTable dt = Utility.LINQToDataTable(DGiaNhomNN);
                    string bacthang = dt.Rows[0]["BAC_THANG"].ToString();
                    if (bacthang == "0")
                    {
                        dt.TableName = "GIA_NHOM_NN";
                        DataSet ds = new DataSet();
                        ds.Tables.Add(dt);
                        return ds;
                    }
                }

                long[] ArrID = DGiaNhomNN.Select(c => c.ID_GIANHOMNN).ToArray();

                long ID = ArrID[0];

                var Dbacthang = from p in CMIS2.DB.D_BAC_THANG
                                where p.GIANHOMNN_ID == ID
                                orderby p.STT_BTHANG
                                select p;
                                    

                if (Dbacthang != null && Dbacthang.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(Dbacthang);
                    dt.TableName = "BAC_THANG";
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }

                return null;
            }
            catch
            { return null; }
        }

        public DataSet Get_Sanluong_Cancu_Dtu(string strMa_dviqly, string strMa_ddo, DateTime dtNgaystop, int intThangHD, int intNamHD)
        {
            try
            {
                #region Lấy theo hóa đơn

                /*
                var HDN_CTIET = from p in CMIS2.DB.HDN_HDONCTIET
                                where p.MA_DDO == strMa_ddo
                                group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                select new 
                                {
                                    grp.Key.MA_DDO,
                                    grp.Key.BCS,
                                    grp.Key.KY,
                                    grp.Key.THANG,
                                    grp.Key.NAM,
                                    SAN_LUONG = grp.Sum(c=>c.DIEN_TTHU)
                                };

                var hdnctiet = from p in CMIS2.DB.HDN_HDONCTIET
                               where p.MA_DDO == strMa_ddo
                               select p;

                long[] ArrID_HDON = hdnctiet.Select(c => c.ID_HDON).Distinct().ToArray();

                var KyHDN = from p in CMIS2.DB.HDN_HDON
                            where ArrID_HDON.Contains(p.ID_HDON)
                            group p by new { p.KY, p.THANG, p.NAM, p.NGAY_DKY, p.NGAY_CKY } into grp
                            select new 
                            {
                                grp.Key.KY,
                                grp.Key.THANG,
                                grp.Key.NAM,
                                grp.Key.NGAY_DKY,
                                grp.Key.NGAY_CKY
                            };

                var gcsCHISO = from p in CMIS2.DB.GCS_CHISO
                               where p.MA_DDO == strMa_ddo
                               select new 
                               {
                                   p.ID_CHISO,
                                   p.BCS,
                                   p.MA_DDO,
                                   p.HS_NHAN,
                                   p.KY,
                                   p.THANG,
                                   p.NAM,
                                   p.CHISO_MOI,
                                   p.CHISO_CU,
                                   p.SAN_LUONG,
                                   p.NGAY_DKY,
                                   p.NGAY_CKY,
                                   p.LOAI_CHISO,
                                   p.SO_CTO

                               };               
                

                var HDN_DC = from p in CMIS2.DB.HDN_HDONCTIET_DC
                             where p.MA_DDO == strMa_ddo
                             group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                             select new
                             {
                                 grp.Key.MA_DDO,
                                 grp.Key.BCS,
                                 grp.Key.KY,
                                 grp.Key.THANG,
                                 grp.Key.NAM,
                                 SAN_LUONG = grp.Sum(g => g.DIEN_TTHU)
                             };

                var join1 = from p in HDN_CTIET.ToList()
                            join m in KyHDN.ToList()
                            on new { p.KY, p.THANG, p.NAM }
                            equals new { m.KY, m.THANG, m.NAM }
                            select new 
                            {
                                p.MA_DDO,
                                p.BCS,
                                p.KY,
                                p.THANG,
                                p.NAM,
                                p.SAN_LUONG,
                                m.NGAY_DKY,
                                m.NGAY_CKY
                            };



        
            var HDN_END = from p in join1.ToList()
                          join m in HDN_DC.ToList()
                          on new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM }
                          equals new { m.MA_DDO, m.BCS, m.KY, m.THANG, m.NAM  } into grp
                          from grp1 in grp.DefaultIfEmpty()
                          orderby p.NAM,p.THANG,p.KY,p.BCS 
                          select new
                          {
                              p.MA_DDO,
                              p.BCS,
                              p.KY,
                              p.THANG,
                              p.NAM,
                              p.NGAY_DKY,
                              p.NGAY_CKY,
                              SLUONG_END =  p.SAN_LUONG + (grp1== null? Convert.ToDecimal(0): Convert.ToDecimal(grp1.SAN_LUONG))
                          };

            if (HDN_END != null && HDN_END.Count() > 0)
            {
                DataTable dt = Utility.LINQToDataTable(HDN_END);
                dt.TableName = "SAN_LUONG";
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                return ds;
            }
                */
                #endregion

                #region Lấy theo GCS_CHISO

                var gcsCHISOHtai = (from p in CMIS2.DB.GCS_CHISO
                                    where p.MA_DDO == strMa_ddo
                                    && p.NGAY_CKY < dtNgaystop
                                    select new
                                    {
                                        p.MA_DVIQLY,
                                        p.ID_CHISO,
                                        p.ID_BCS,
                                        p.MA_DDO,
                                        p.BCS,
                                        p.HS_NHAN,
                                        p.MA_CTO,
                                        p.SO_CTO,
                                        p.KY,
                                        p.THANG,
                                        p.NAM,
                                        p.CHISO_CU,
                                        p.CHISO_MOI,
                                        p.SAN_LUONG,
                                        p.SLUONG_TTIEP,
                                        p.SLUONG_TRPHU,
                                        p.LOAI_CHISO,
                                        p.MA_TTCTO,
                                        p.NGAY_DKY,
                                        p.NGAY_CKY,
                                        p.SLUONG_1,
                                        p.SLUONG_2,
                                        p.SLUONG_3,
                                        p.MA_KHANG,
                                        p.NGAY_TAO,
                                        p.NGUOI_TAO,
                                        p.NGAY_SUA,
                                        p.NGUOI_SUA,
                                        p.MA_CNANG
                                    }).ToList();

                var gcsCHISOLsu = (from p in CMIS2.DB.GCS_CHISO_LSU
                                   where p.MA_DDO == strMa_ddo
                                   && p.NGAY_CKY < dtNgaystop
                                   select new
                                   {
                                       p.MA_DVIQLY,
                                       p.ID_CHISO,
                                       p.ID_BCS,
                                       p.MA_DDO,
                                       p.BCS,
                                       p.HS_NHAN,
                                       p.MA_CTO,
                                       p.SO_CTO,
                                       p.KY,
                                       p.THANG,
                                       p.NAM,
                                       p.CHISO_CU,
                                       p.CHISO_MOI,
                                       p.SAN_LUONG,
                                       p.SLUONG_TTIEP,
                                       p.SLUONG_TRPHU,
                                       p.LOAI_CHISO,
                                       p.MA_TTCTO,
                                       p.NGAY_DKY,
                                       p.NGAY_CKY,
                                       p.SLUONG_1,
                                       p.SLUONG_2,
                                       p.SLUONG_3,
                                       p.MA_KHANG,
                                       p.NGAY_TAO,
                                       p.NGUOI_TAO,
                                       p.NGAY_SUA,
                                       p.NGUOI_SUA,
                                       p.MA_CNANG
                                   }).ToList();

                var gcsCHISO = gcsCHISOHtai.Union(gcsCHISOLsu);

                var gcsCHISOMaxID = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c2 => c2.MA_DDO == c.MA_DDO).Max(c3 => c3.ID_CHISO));

                DataTable dtkhacDDK = new DataTable(); // luu Ky gan nhat trong truong hop ky nay co treo thao và chưa tròn kỳ - chưa ra hóa đơn.
                if (gcsCHISOMaxID != null && gcsCHISOMaxID.Count() > 0)
                {
                    DataTable dt1 = Utility.LINQToDataTable(gcsCHISOMaxID);

                    if (dt1.Rows[0]["LOAI_CHISO"].ToString() != "DDK")
                    {
                        dtkhacDDK = dt1.Copy();
                    }

                }

                string[] ArrMacto = gcsCHISOMaxID.Select(c => c.MA_CTO).Distinct().ToArray();
                string Macto = ArrMacto[0];

                

                var GCS_CHISO_grpHtai = (from p in CMIS2.DB.GCS_CHISO
                                      where p.MA_DDO == strMa_ddo
                                      //&& p.MA_CTO == Macto
                                      && p.NGAY_CKY < dtNgaystop
                                         select new
                                         {
                                             p.MA_DVIQLY,
                                             p.ID_CHISO,
                                             p.ID_BCS,
                                             p.MA_DDO,
                                             p.BCS,
                                             p.HS_NHAN,
                                             p.MA_CTO,
                                             p.SO_CTO,
                                             p.KY,
                                             p.THANG,
                                             p.NAM,
                                             p.CHISO_CU,
                                             p.CHISO_MOI,
                                             p.SAN_LUONG,
                                             p.SLUONG_TTIEP,
                                             p.SLUONG_TRPHU,
                                             p.LOAI_CHISO,
                                             p.MA_TTCTO,
                                             p.NGAY_DKY,
                                             p.NGAY_CKY,
                                             p.SLUONG_1,
                                             p.SLUONG_2,
                                             p.SLUONG_3,
                                             p.MA_KHANG,
                                             p.NGAY_TAO,
                                             p.NGUOI_TAO,
                                             p.NGAY_SUA,
                                             p.NGUOI_SUA,
                                             p.MA_CNANG
                                         }).ToList();

                var GCS_CHISO_grpLsu = (from p in CMIS2.DB.GCS_CHISO_LSU
                                         where p.MA_DDO == strMa_ddo
                                         //&& p.MA_CTO == Macto
                                         && p.NGAY_CKY < dtNgaystop
                                         select new
                                         {
                                             p.MA_DVIQLY,
                                             p.ID_CHISO,
                                             p.ID_BCS,
                                             p.MA_DDO,
                                             p.BCS,
                                             p.HS_NHAN,
                                             p.MA_CTO,
                                             p.SO_CTO,
                                             p.KY,
                                             p.THANG,
                                             p.NAM,
                                             p.CHISO_CU,
                                             p.CHISO_MOI,
                                             p.SAN_LUONG,
                                             p.SLUONG_TTIEP,
                                             p.SLUONG_TRPHU,
                                             p.LOAI_CHISO,
                                             p.MA_TTCTO,
                                             p.NGAY_DKY,
                                             p.NGAY_CKY,
                                             p.SLUONG_1,
                                             p.SLUONG_2,
                                             p.SLUONG_3,
                                             p.MA_KHANG,
                                             p.NGAY_TAO,
                                             p.NGUOI_TAO,
                                             p.NGAY_SUA,
                                             p.NGUOI_SUA,
                                             p.MA_CNANG
                                         }).ToList();

                var GCS_CHISO_grpA = GCS_CHISO_grpHtai.Union(GCS_CHISO_grpLsu);

                var GCS_CHISO_grpB = from p in GCS_CHISO_grpA
                                     where p.MA_DDO == strMa_ddo
                                     //&& p.MA_CTO == Macto
                                     && p.NGAY_CKY < dtNgaystop
                                     select new
                                     {
                                         p.MA_DVIQLY,
                                         p.ID_CHISO,
                                         p.ID_BCS,
                                         p.MA_DDO,
                                         p.BCS,
                                         p.HS_NHAN,
                                         p.MA_CTO,
                                         p.SO_CTO,
                                         p.KY,
                                         p.THANG,
                                         p.NAM,
                                         p.CHISO_CU,
                                         p.CHISO_MOI,
                                         SAN_LUONG = p.SAN_LUONG == null ? 0 : p.SAN_LUONG,
                                         SLUONG_TTIEP = p.SLUONG_TTIEP == null ? 0 : p.SLUONG_TTIEP,
                                         SLUONG_TRPHU = p.SLUONG_TRPHU == null ? 0 : p.SLUONG_TRPHU,
                                         p.LOAI_CHISO,
                                         p.MA_TTCTO,
                                         p.NGAY_DKY,
                                         p.NGAY_CKY
                                     };

                var GCS_CHISO_grp = from p in GCS_CHISO_grpB  // CMIS2.DB.GCS_CHISO
                                     where p.MA_DDO == strMa_ddo
                                     //&& p.MA_CTO == Macto
                                     && p.NGAY_CKY < dtNgaystop
                                     group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                     select new
                                     {
                                         grp.Key.MA_DDO,
                                         grp.Key.BCS,
                                         grp.Key.KY,
                                         grp.Key.THANG,
                                         grp.Key.NAM,
                                         SAN_LUONG = grp.Sum(c => c.SAN_LUONG) + grp.Sum(c1 => c1.SLUONG_TTIEP) + grp.Sum(c2 => c2.SLUONG_TRPHU)
                                     };

                var kythangnamngay = (from p in gcsCHISO
                                      where p.MA_DDO == strMa_ddo
                                      group p by new { p.KY, p.THANG, p.NAM, p.NGAY_DKY, p.NGAY_CKY } into grp
                                      select new
                                      {
                                          grp.Key.KY,
                                          grp.Key.THANG,
                                          grp.Key.NAM,
                                          grp.Key.NGAY_DKY,
                                          grp.Key.NGAY_CKY
                                      }).ToList();

                var kythangnam = (from p in gcsCHISO
                                  where p.MA_DDO == strMa_ddo
                                  group p by new { p.KY, p.THANG, p.NAM } into grp
                                  orderby grp.Key.NAM, grp.Key.THANG, grp.Key.KY
                                  select new
                                  {
                                      grp.Key.KY,
                                      grp.Key.THANG,
                                      grp.Key.NAM
                                  }).ToList();

                var kyEND = from p in kythangnam
                            select new
                            {
                                p.KY,
                                p.THANG,
                                p.NAM,
                                NGAY_DKY = kythangnamngay.Where(c => c.KY == p.KY && c.THANG == p.THANG && c.NAM == p.NAM).Min(c1 => c1.NGAY_DKY),
                                NGAY_CKY = kythangnamngay.Where(d => d.KY == p.KY && d.THANG == p.THANG && d.NAM == p.NAM).Max(c2 => c2.NGAY_CKY)
                            };

                
                var GCS_CHISO_DC_grp = from p in CMIS2.DB.HDN_CHISO_DC
                                       where p.MA_DDO == strMa_ddo
                                       //&& p.MA_CTO == Macto
                                       group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                       select new
                                       {
                                           grp.Key.MA_DDO,
                                           grp.Key.BCS,
                                           grp.Key.KY,
                                           grp.Key.THANG,
                                           grp.Key.NAM,
                                           SAN_LUONG = grp.Sum(c => c.SAN_LUONG)
                                       };

                //var union = GCS_CHISO_grp.Union(GCS_CHISO_DC_grp);

                var GCS_CHISO_grpJoinkyEND = from p in GCS_CHISO_grp.ToList()
                                             join m in kyEND.ToList()
                                             on new { p.KY, p.THANG, p.NAM }
                                             equals new { m.KY, m.THANG, m.NAM }
                                             select new
                                             {
                                                 p.MA_DDO,
                                                 p.BCS,
                                                 p.KY,
                                                 p.THANG,
                                                 p.NAM,
                                                 p.SAN_LUONG,
                                                 NGAY_DKY = m.NGAY_DKY,
                                                 NGAY_CKY = m.NGAY_CKY
                                             };

                var HDN_DC = from p in CMIS2.DB.HDN_HDONCTIET_DC
                             where p.MA_DDO == strMa_ddo
                             group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                             select new
                             {
                                 grp.Key.MA_DDO,
                                 grp.Key.BCS,
                                 grp.Key.KY,
                                 grp.Key.THANG,
                                 grp.Key.NAM,
                                 SAN_LUONG = grp.Sum(g => g.DIEN_TTHU)
                             };


                var joi1 = from p in GCS_CHISO_grpJoinkyEND.ToList()
                           join m in HDN_DC.ToList()
                           on new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM }
                           equals new { m.MA_DDO, m.BCS, m.KY, m.THANG, m.NAM } into grp
                           from grp1 in grp.DefaultIfEmpty()
                           orderby p.NAM, p.THANG, p.KY, p.BCS
                           select new
                           {
                               p.MA_DDO,
                               p.BCS,
                               p.KY,
                               p.THANG,
                               p.NAM,
                               p.NGAY_DKY,
                               p.NGAY_CKY,
                               SLUONG_END = p.SAN_LUONG + (grp1 == null ? Convert.ToDecimal(0) : Convert.ToDecimal(grp1.SAN_LUONG))
                           };


                var q1 = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c1 => c1.MA_DDO == c.MA_DDO && c1.BCS == c.BCS).Max(c2 => c2.ID_CHISO));

                var gcsCHISO_gannhat = from p in q1
                                       where p.MA_DDO == strMa_ddo
                                       select new 
                                       {
                                           p.MA_DVIQLY,
                                           p.ID_CHISO,
                                           p.ID_BCS,
                                           p.MA_DDO,
                                           p.BCS,
                                           p.HS_NHAN,
                                           p.MA_CTO,
                                           p.SO_CTO,
                                           p.KY,
                                           p.THANG,
                                           p.NAM,
                                           p.CHISO_CU,
                                           p.CHISO_MOI,
                                           p.SAN_LUONG,
                                           p.SLUONG_TTIEP,
                                           p.SLUONG_TRPHU,
                                           p.LOAI_CHISO,
                                           p.MA_TTCTO,
                                           p.NGAY_DKY,
                                           p.NGAY_CKY,
                                           p.SLUONG_1,
                                           p.SLUONG_2,
                                           p.SLUONG_3,
                                           p.MA_KHANG,
                                           p.NGAY_TAO,
                                           p.NGUOI_TAO,
                                           p.NGAY_SUA,
                                           p.NGUOI_SUA,
                                           p.MA_CNANG
                                       };
                

                // lay chung loai
                var HOSOCTO = (from p in CMIS2.DB.DD_HOSO_CTO
                              where p.MA_DVIQLY == strMa_dviqly
                              && p.MA_CTO == Macto
                              select p).ToList();

                string[] ArrMacloai = HOSOCTO.Select(c => c.MA_CLOAI).Distinct().ToArray();
                string Macloaicto = ArrMacloai[0];

                var LOAICTO = from p in CMIS2.DB.D_CLOAI_CTO
                              where p.MA_CLOAI == Macloaicto
                              select new 
                              {
                                  p.MA_CLOAI,
                                  p.LOAI_CTO,
                                  p.MO_TA,
                                  p.SO_PHA,
                                  p.SO_DAY,
                                  p.HS_NHAN,
                                  p.SO_CSO,
                                  p.CAP_CXAC,
                                  p.DONG_DIEN,
                                  p.DIEN_AP,
                                  p.VH_CONG,
                                  p.MA_NUOC,
                                  p.MA_HANG,
                                  p.HANGSO_K
                              };



                if (joi1 != null && joi1.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(joi1);
                    dt.TableName = "SAN_LUONG";

                    DataTable dtDDN = new DataTable(); // bỏ đi kỳ gần nhất nếu không tròn kỳ trong sản lượng căn cứ
                    if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0)
                    {
                        DataRow[] Arr = dt.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());
                        dtDDN = Arr.CopyToDataTable();
                        dtDDN.TableName = "DDN";

                        for (int i = 0; i < Arr.Count(); i++)
                        {
                            dt.Rows.Remove(Arr[i]);
                        }
                        dt.AcceptChanges();
                    }


                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);

                    if (dtDDN != null && dtDDN.Rows.Count > 0)
                    {
                        ds.Tables.Add(dtDDN);
                    }

                    if (gcsCHISO_gannhat != null && gcsCHISO_gannhat.Count() > 0)
                    {
                        DataTable dtcs = Utility.LINQToDataTable(gcsCHISO_gannhat);
                        dtcs.TableName = "CHI_SO";
                        ds.Tables.Add(dtcs);
                    }

                    if (kythangnam != null && kythangnam.Count() > 0)
                    {
                        DataTable dtKTN = Utility.LINQToDataTable(kythangnam);
                        dtKTN.TableName = "KYTHANGNAM";

                        if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0) // bỏ kỳ tháng năm  không tròn kỳ trong kỳ tháng năm căn cứ
                        {
                            DataRow[] Arr = dtKTN.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());                            

                            for (int i = 0; i < Arr.Count(); i++)
                            {
                                dtKTN.Rows.Remove(Arr[i]);
                            }
                            dtKTN.AcceptChanges();
                        }

                        ds.Tables.Add(dtKTN);
                    }

                    if (LOAICTO != null && LOAICTO.Count() > 0)
                    {
                        DataTable dtLCTO = Utility.LINQToDataTable(LOAICTO);
                        dtLCTO.TableName = "LOAICTO";
                        ds.Tables.Add(dtLCTO);
                    }

                    return ds;
                }

                #endregion

                return null;

            }
            catch
            { return null; }

        }

        public DataSet Get_Sanluong_Cancu_Co(string strMa_ddo, DateTime dtNgaystop)
        {
            try
            {
                #region Lấy theo hóa đơn
                /* 
                 * Lấy theo hóa đơn
                var HDN_CTIET = from p in CMIS2.DB.HDN_HDONCTIET
                                where p.MA_DDO == strMa_ddo
                                && p.BCS == "KT"
                                group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                select new
                                {
                                    grp.Key.MA_DDO,
                                    grp.Key.BCS,
                                    grp.Key.KY,
                                    grp.Key.THANG,
                                    grp.Key.NAM,
                                    SAN_LUONG = grp.Sum(c => c.DIEN_TTHU)
                                };

                var hdnctiet = from p in CMIS2.DB.HDN_HDONCTIET
                               where p.MA_DDO == strMa_ddo
                               select p;

                long[] ArrID_HDON = hdnctiet.Select(c => c.ID_HDON).Distinct().ToArray();

                var KyHDN = from p in CMIS2.DB.HDN_HDON
                            where ArrID_HDON.Contains(p.ID_HDON)
                            group p by new { p.KY, p.THANG, p.NAM, p.NGAY_DKY, p.NGAY_CKY } into grp
                            select new
                            {
                                grp.Key.KY,
                                grp.Key.THANG,
                                grp.Key.NAM,
                                grp.Key.NGAY_DKY,
                                grp.Key.NGAY_CKY
                            };

                var gcsCHISO = (from p in CMIS2.DB.GCS_CHISO
                               where p.MA_DDO == strMa_ddo
                               && p.BCS == "KT"
                               select new
                               {
                                   p.ID_CHISO,
                                   p.BCS,
                                   p.MA_DDO,
                                   p.HS_NHAN,
                                   p.KY,
                                   p.THANG,
                                   p.NAM,
                                   p.CHISO_MOI,
                                   p.CHISO_CU,
                                   p.SAN_LUONG,
                                   p.NGAY_DKY,
                                   p.NGAY_CKY,
                                   p.LOAI_CHISO,
                                   p.SO_CTO
                               }).ToList();

                var gcsCHISO_gannhat = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c1 => c1.MA_DDO == c.MA_DDO).Max(c2 => c2.ID_CHISO));


                var HDN_DC = from p in CMIS2.DB.HDN_HDONCTIET_DC
                             where p.MA_DDO == strMa_ddo
                             group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                             select new
                             {
                                 grp.Key.MA_DDO,
                                 grp.Key.BCS,
                                 grp.Key.KY,
                                 grp.Key.THANG,
                                 grp.Key.NAM,
                                 SAN_LUONG = grp.Sum(g => g.DIEN_TTHU)
                             };

                var join1 = from p in HDN_CTIET.ToList()
                            join m in KyHDN.ToList()
                            on new { p.KY, p.THANG, p.NAM }
                            equals new { m.KY, m.THANG, m.NAM }
                            select new
                            {
                                p.MA_DDO,
                                p.BCS,
                                p.KY,
                                p.THANG,
                                p.NAM,
                                p.SAN_LUONG,
                                m.NGAY_DKY,
                                m.NGAY_CKY
                            };


                var HDN_END = from p in join1.ToList()
                              join m in HDN_DC.ToList()
                              on new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM }
                              equals new { m.MA_DDO, m.BCS, m.KY, m.THANG, m.NAM } into grp
                              from grp1 in grp.DefaultIfEmpty()
                              orderby p.NAM, p.THANG, p.KY, p.BCS
                              select new
                              {
                                  p.MA_DDO,
                                  p.BCS,
                                  p.KY,
                                  p.THANG,
                                  p.NAM,
                                  p.NGAY_DKY,
                                  p.NGAY_CKY,
                                  SLUONG_END = p.SAN_LUONG + (grp1 == null ? Convert.ToDecimal(0) : Convert.ToDecimal(grp1.SAN_LUONG))
                              };

                if (HDN_END != null && HDN_END.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(HDN_END);
                    dt.TableName = "SAN_LUONG";
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    if (gcsCHISO_gannhat != null && gcsCHISO_gannhat.Count() > 0)
                    {
                        DataTable dtcs = Utility.LINQToDataTable(gcsCHISO_gannhat);
                        dtcs.TableName = "CHI_SO";
                        ds.Tables.Add(dtcs);
                    }
                    return ds;
                }
                 * */
                #endregion


                #region Lấy theo GCS_CHISO

                var gcsCHISOLsu = (from p in CMIS2.DB.GCS_CHISO_LSU
                                  where p.MA_DDO == strMa_ddo
                                  && p.BCS == "KT"
                                  && p.NGAY_CKY < dtNgaystop
                                  select new 
                                    {
                                        p.MA_DVIQLY,
                                        p.ID_CHISO,
                                        p.ID_BCS,
                                        p.MA_DDO,
                                        p.BCS,
                                        p.HS_NHAN,
                                        p.MA_CTO,
                                        p.SO_CTO,
                                        p.KY,
                                        p.THANG,
                                        p.NAM,
                                        p.CHISO_CU,
                                        p.CHISO_MOI,
                                        p.SAN_LUONG,
                                        p.SLUONG_TTIEP,
                                        p.SLUONG_TRPHU,
                                        p.LOAI_CHISO,
                                        p.MA_TTCTO,
                                        p.NGAY_DKY,
                                        p.NGAY_CKY,
                                        p.SLUONG_1,
                                        p.SLUONG_2,
                                        p.SLUONG_3,
                                        p.MA_KHANG,
                                        p.NGAY_TAO,
                                        p.NGUOI_TAO,
                                        p.NGAY_SUA,
                                        p.NGUOI_SUA,
                                        p.MA_CNANG
                                    }).ToList();

                var gcsCHISOHtai = (from p in CMIS2.DB.GCS_CHISO
                                    where p.MA_DDO == strMa_ddo
                                    && p.BCS == "KT"
                                    && p.NGAY_CKY < dtNgaystop
                                    select new 
                                    {
                                        p.MA_DVIQLY,
                                        p.ID_CHISO,
                                        p.ID_BCS,
                                        p.MA_DDO,
                                        p.BCS,
                                        p.HS_NHAN,
                                        p.MA_CTO,
                                        p.SO_CTO,
                                        p.KY,
                                        p.THANG,
                                        p.NAM,
                                        p.CHISO_CU,
                                        p.CHISO_MOI,
                                        p.SAN_LUONG,
                                        p.SLUONG_TTIEP,
                                        p.SLUONG_TRPHU,
                                        p.LOAI_CHISO,
                                        p.MA_TTCTO,
                                        p.NGAY_DKY,
                                        p.NGAY_CKY,
                                        p.SLUONG_1,
                                        p.SLUONG_2,
                                        p.SLUONG_3,
                                        p.MA_KHANG,
                                        p.NGAY_TAO,
                                        p.NGUOI_TAO,
                                        p.NGAY_SUA,
                                        p.NGUOI_SUA,
                                        p.MA_CNANG
                                    }).ToList();

                var gcsCHISO = gcsCHISOHtai.Union(gcsCHISOLsu);

                var gcsCHISOMaxID = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c2 => c2.MA_DDO == c.MA_DDO).Max(c3 => c3.ID_CHISO));

                DataTable dtkhacDDK = new DataTable(); // luu Ky gan nhat trong truong hop ky nay co treo thao và chưa tròn kỳ - chưa ra hóa đơn.
                if (gcsCHISOMaxID != null && gcsCHISOMaxID.Count() > 0)
                {
                    DataTable dt1 = Utility.LINQToDataTable(gcsCHISOMaxID);

                    if (dt1.Rows[0]["LOAI_CHISO"].ToString() != "DDK")
                    {
                        dtkhacDDK = dt1.Copy();
                    }
                    
                }

                string[] ArrMacto = gcsCHISOMaxID.Select(c => c.MA_CTO).Distinct().ToArray();
                string Macto = ArrMacto[0];

                var GCS_CHISO_grpHtai = (from p in CMIS2.DB.GCS_CHISO
                                     where p.MA_DDO == strMa_ddo
                                     && p.BCS == "KT" // //&& p.MA_CTO == Macto
                                     && p.NGAY_CKY < dtNgaystop
                                         select new
                                         {
                                             p.MA_DVIQLY,
                                             p.ID_CHISO,
                                             p.ID_BCS,
                                             p.MA_DDO,
                                             p.BCS,
                                             p.HS_NHAN,
                                             p.MA_CTO,
                                             p.SO_CTO,
                                             p.KY,
                                             p.THANG,
                                             p.NAM,
                                             p.CHISO_CU,
                                             p.CHISO_MOI,
                                             p.SAN_LUONG,
                                             p.SLUONG_TTIEP,
                                             p.SLUONG_TRPHU,
                                             p.LOAI_CHISO,
                                             p.MA_TTCTO,
                                             p.NGAY_DKY,
                                             p.NGAY_CKY,
                                             p.SLUONG_1,
                                             p.SLUONG_2,
                                             p.SLUONG_3,
                                             p.MA_KHANG,
                                             p.NGAY_TAO,
                                             p.NGUOI_TAO,
                                             p.NGAY_SUA,
                                             p.NGUOI_SUA,
                                             p.MA_CNANG
                                         }).ToList();

                var GCS_CHISO_grpLsu = (from p in CMIS2.DB.GCS_CHISO_LSU
                                         where p.MA_DDO == strMa_ddo
                                         && p.BCS == "KT" // //&& p.MA_CTO == Macto
                                         && p.NGAY_CKY < dtNgaystop
                                        select new
                                        {
                                            p.MA_DVIQLY,
                                            p.ID_CHISO,
                                            p.ID_BCS,
                                            p.MA_DDO,
                                            p.BCS,
                                            p.HS_NHAN,
                                            p.MA_CTO,
                                            p.SO_CTO,
                                            p.KY,
                                            p.THANG,
                                            p.NAM,
                                            p.CHISO_CU,
                                            p.CHISO_MOI,
                                            p.SAN_LUONG,
                                            p.SLUONG_TTIEP,
                                            p.SLUONG_TRPHU,
                                            p.LOAI_CHISO,
                                            p.MA_TTCTO,
                                            p.NGAY_DKY,
                                            p.NGAY_CKY,
                                            p.SLUONG_1,
                                            p.SLUONG_2,
                                            p.SLUONG_3,
                                            p.MA_KHANG,
                                            p.NGAY_TAO,
                                            p.NGUOI_TAO,
                                            p.NGAY_SUA,
                                            p.NGUOI_SUA,
                                            p.MA_CNANG
                                        }).ToList();

                var GCS_CHISO_grpA = GCS_CHISO_grpHtai.Union(GCS_CHISO_grpLsu);

                var GCS_CHISO_grpB = from p in GCS_CHISO_grpA
                                     where p.MA_DDO == strMa_ddo
                                     && p.BCS == "KT"
                                     && p.NGAY_CKY < dtNgaystop
                                     select new 
                                     {
                                         p.MA_DVIQLY,
                                         p.ID_CHISO,
                                         p.ID_BCS,
                                         p.MA_DDO,
                                         p.BCS,
                                         p.HS_NHAN,
                                         p.MA_CTO,
                                         p.SO_CTO,
                                         p.KY,
                                         p.THANG,
                                         p.NAM,
                                         p.CHISO_CU,
                                         p.CHISO_MOI,
                                         SAN_LUONG = p.SAN_LUONG == null ? 0 : p.SAN_LUONG,
                                         SLUONG_TTIEP = p.SLUONG_TTIEP == null ? 0 : p.SLUONG_TTIEP,
                                         SLUONG_TRPHU = p.SLUONG_TRPHU == null ? 0 : p.SLUONG_TRPHU,
                                         p.LOAI_CHISO,
                                         p.MA_TTCTO,
                                         p.NGAY_DKY,
                                         p.NGAY_CKY
                                     };


                var GCS_CHISO_grp = from p in GCS_CHISO_grpB // CMIS2.DB.GCS_CHISO
                                    where p.MA_DDO == strMa_ddo
                                    && p.BCS == "KT" // && p.MA_CTO == Macto
                                    && p.NGAY_CKY < dtNgaystop
                                    group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                    select new
                                    {
                                        grp.Key.MA_DDO,
                                        grp.Key.BCS,
                                        grp.Key.KY,
                                        grp.Key.THANG,
                                        grp.Key.NAM,
                                        SAN_LUONG = grp.Sum(c => c.SAN_LUONG) + grp.Sum(c1 => c1.SLUONG_TTIEP) + grp.Sum(c2 => c2.SLUONG_TRPHU)
                                    };

                var kythangnamngay = (from p in gcsCHISO
                                      where p.MA_DDO == strMa_ddo
                                      group p by new { p.KY, p.THANG, p.NAM, p.NGAY_DKY, p.NGAY_CKY } into grp
                                      select new
                                      {
                                          grp.Key.KY,
                                          grp.Key.THANG,
                                          grp.Key.NAM,
                                          grp.Key.NGAY_DKY,
                                          grp.Key.NGAY_CKY
                                      }).ToList();

                var kythangnam = (from p in gcsCHISO
                                  where p.MA_DDO == strMa_ddo
                                  group p by new { p.KY, p.THANG, p.NAM } into grp
                                  select new
                                  {
                                      grp.Key.KY,
                                      grp.Key.THANG,
                                      grp.Key.NAM
                                  }).ToList();

                var kyEND = from p in kythangnam
                            select new
                            {
                                p.KY,
                                p.THANG,
                                p.NAM,
                                NGAY_DKY = kythangnamngay.Where(c => c.KY == p.KY && c.THANG == p.THANG && c.NAM == p.NAM).Min(c1 => c1.NGAY_DKY),
                                NGAY_CKY = kythangnamngay.Where(d => d.KY == p.KY && d.THANG == p.THANG && d.NAM == p.NAM).Max(c2 => c2.NGAY_CKY)
                            };

                var GCS_CHISO_DC_grp = from p in CMIS2.DB.HDN_CHISO_DC
                                       where p.MA_DDO == strMa_ddo
                                       && p.BCS == "KT"
                                       group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                       select new
                                       {
                                           grp.Key.MA_DDO,
                                           grp.Key.BCS,
                                           grp.Key.KY,
                                           grp.Key.THANG,
                                           grp.Key.NAM,
                                           SAN_LUONG = grp.Sum(c => c.SAN_LUONG)
                                       };

                //var union = GCS_CHISO_grp.Union(GCS_CHISO_DC_grp);

                var GCS_CHISO_grpJoinkyEND = from p in GCS_CHISO_grp.ToList()
                                             join m in kyEND.ToList()
                                             on new { p.KY, p.THANG, p.NAM }
                                             equals new { m.KY, m.THANG, m.NAM }
                                             select new 
                                             {
                                                 p.MA_DDO,
                                                 p.BCS,
                                                 p.KY,
                                                 p.THANG,
                                                 p.NAM,
                                                 p.SAN_LUONG,
                                                 NGAY_DKY =  m.NGAY_DKY,
                                                 NGAY_CKY = m.NGAY_CKY
                                             };

                var HDN_DC = from p in CMIS2.DB.HDN_HDONCTIET_DC
                             where p.MA_DDO == strMa_ddo
                             group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                             select new
                             {
                                 grp.Key.MA_DDO,
                                 grp.Key.BCS,
                                 grp.Key.KY,
                                 grp.Key.THANG,
                                 grp.Key.NAM,
                                 SAN_LUONG = grp.Sum(g => g.DIEN_TTHU)
                             };


                var joi1 = from p in GCS_CHISO_grpJoinkyEND.ToList()
                           join m in HDN_DC.ToList()
                           on new{p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM }
                           equals new{m.MA_DDO, m.BCS, m.KY, m.THANG, m.NAM } into  grp
                           from grp1 in grp.DefaultIfEmpty()
                           orderby p.NAM, p.THANG, p.KY, p.BCS
                           select new
                           {
                               p.MA_DDO,
                               p.BCS,
                               p.KY,
                               p.THANG,
                               p.NAM,
                               p.NGAY_DKY,
                               p.NGAY_CKY,
                               SLUONG_END = p.SAN_LUONG + (grp1 == null ? Convert.ToDecimal(0) : Convert.ToDecimal(grp1.SAN_LUONG))
                           };
                

                var gcsCHISO_gannhat = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c1 => c1.MA_DDO == c.MA_DDO).Max(c2 => c2.ID_CHISO));



                if (joi1 != null && joi1.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(joi1);
                    dt.TableName = "SAN_LUONG";

                    DataTable dtDDN = new DataTable();
                    if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0)
                    {
                        DataRow[] Arr = dt.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());
                        dtDDN = Arr.CopyToDataTable();
                        dtDDN.TableName = "DDN";

                        for (int i = 0; i < Arr.Count(); i++)
                        {
                            dt.Rows.Remove(Arr[i]);                            
                        }
                        dt.AcceptChanges();
                    }

                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);

                    if (dtDDN != null && dtDDN.Rows.Count > 0)
                    {
                        ds.Tables.Add(dtDDN);
                    }

                    if (gcsCHISO_gannhat != null && gcsCHISO_gannhat.Count() > 0)
                    {
                        DataTable dtcs = Utility.LINQToDataTable(gcsCHISO_gannhat);
                        dtcs.TableName = "CHI_SO";
                        ds.Tables.Add(dtcs);
                    }
                    return ds;
                }

                #endregion


                return null;

            }
            catch
            { return null; }

        }

        public DataSet Get_Sanluong_Cancu_Dtu_After(string strMa_dviqly, string strMa_ddo, DateTime dtNgaydau, DateTime dtNgaycuoi, int intThangHD, int intNamHD)
        {
            try
            {
                #region Lấy theo GCS_CHISO

                #region Hien tai

                var gcsCHISOHtai = (from p in CMIS2.DB.GCS_CHISO
                                    where p.MA_DDO == strMa_ddo
                                    && p.NGAY_CKY < dtNgaydau
                                    select new
                                    {
                                        p.MA_DVIQLY,
                                        p.ID_CHISO,
                                        p.ID_BCS,
                                        p.MA_DDO,
                                        p.BCS,
                                        p.HS_NHAN,
                                        p.MA_CTO,
                                        p.SO_CTO,
                                        p.KY,
                                        p.THANG,
                                        p.NAM,
                                        p.CHISO_CU,
                                        p.CHISO_MOI,
                                        p.SAN_LUONG,
                                        p.SLUONG_TTIEP,
                                        p.SLUONG_TRPHU,
                                        p.LOAI_CHISO,
                                        p.MA_TTCTO,
                                        p.NGAY_DKY,
                                        p.NGAY_CKY,
                                        p.SLUONG_1,
                                        p.SLUONG_2,
                                        p.SLUONG_3,
                                        p.MA_KHANG,
                                        p.NGAY_TAO,
                                        p.NGUOI_TAO,
                                        p.NGAY_SUA,
                                        p.NGUOI_SUA,
                                        p.MA_CNANG
                                    }).ToList();

                var gcsCHISOLsu = (from p in CMIS2.DB.GCS_CHISO_LSU
                                   where p.MA_DDO == strMa_ddo
                                   && p.NGAY_CKY < dtNgaydau
                                   select new
                                   {
                                       p.MA_DVIQLY,
                                       p.ID_CHISO,
                                       p.ID_BCS,
                                       p.MA_DDO,
                                       p.BCS,
                                       p.HS_NHAN,
                                       p.MA_CTO,
                                       p.SO_CTO,
                                       p.KY,
                                       p.THANG,
                                       p.NAM,
                                       p.CHISO_CU,
                                       p.CHISO_MOI,
                                       p.SAN_LUONG,
                                       p.SLUONG_TTIEP,
                                       p.SLUONG_TRPHU,
                                       p.LOAI_CHISO,
                                       p.MA_TTCTO,
                                       p.NGAY_DKY,
                                       p.NGAY_CKY,
                                       p.SLUONG_1,
                                       p.SLUONG_2,
                                       p.SLUONG_3,
                                       p.MA_KHANG,
                                       p.NGAY_TAO,
                                       p.NGUOI_TAO,
                                       p.NGAY_SUA,
                                       p.NGUOI_SUA,
                                       p.MA_CNANG
                                   }).ToList();

                var gcsCHISO = gcsCHISOHtai.Union(gcsCHISOLsu);

                var gcsCHISOMaxID = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c2 => c2.MA_DDO == c.MA_DDO).Max(c3 => c3.ID_CHISO));

                DataTable dtkhacDDK = new DataTable(); // luu Ky gan nhat trong truong hop ky nay co treo thao và chưa tròn kỳ - chưa ra hóa đơn.
                if (gcsCHISOMaxID != null && gcsCHISOMaxID.Count() > 0)
                {
                    DataTable dt1 = Utility.LINQToDataTable(gcsCHISOMaxID);

                    if (dt1.Rows[0]["LOAI_CHISO"].ToString() != "DDK")
                    {
                        dtkhacDDK = dt1.Copy();
                    }

                }

                string[] ArrMacto = gcsCHISOMaxID.Select(c => c.MA_CTO).Distinct().ToArray();
                string Macto = ArrMacto[0];



                var GCS_CHISO_grpHtai = (from p in CMIS2.DB.GCS_CHISO
                                         where p.MA_DDO == strMa_ddo
                                         //&& p.MA_CTO == Macto
                                         && p.NGAY_CKY < dtNgaydau
                                         select new
                                         {
                                             p.MA_DVIQLY,
                                             p.ID_CHISO,
                                             p.ID_BCS,
                                             p.MA_DDO,
                                             p.BCS,
                                             p.HS_NHAN,
                                             p.MA_CTO,
                                             p.SO_CTO,
                                             p.KY,
                                             p.THANG,
                                             p.NAM,
                                             p.CHISO_CU,
                                             p.CHISO_MOI,
                                             p.SAN_LUONG,
                                             p.SLUONG_TTIEP,
                                             p.SLUONG_TRPHU,
                                             p.LOAI_CHISO,
                                             p.MA_TTCTO,
                                             p.NGAY_DKY,
                                             p.NGAY_CKY,
                                             p.SLUONG_1,
                                             p.SLUONG_2,
                                             p.SLUONG_3,
                                             p.MA_KHANG,
                                             p.NGAY_TAO,
                                             p.NGUOI_TAO,
                                             p.NGAY_SUA,
                                             p.NGUOI_SUA,
                                             p.MA_CNANG
                                         }).ToList();

                var GCS_CHISO_grpLsu = (from p in CMIS2.DB.GCS_CHISO_LSU
                                        where p.MA_DDO == strMa_ddo
                                        //&& p.MA_CTO == Macto
                                        && p.NGAY_CKY < dtNgaydau
                                        select new
                                        {
                                            p.MA_DVIQLY,
                                            p.ID_CHISO,
                                            p.ID_BCS,
                                            p.MA_DDO,
                                            p.BCS,
                                            p.HS_NHAN,
                                            p.MA_CTO,
                                            p.SO_CTO,
                                            p.KY,
                                            p.THANG,
                                            p.NAM,
                                            p.CHISO_CU,
                                            p.CHISO_MOI,
                                            p.SAN_LUONG,
                                            p.SLUONG_TTIEP,
                                            p.SLUONG_TRPHU,
                                            p.LOAI_CHISO,
                                            p.MA_TTCTO,
                                            p.NGAY_DKY,
                                            p.NGAY_CKY,
                                            p.SLUONG_1,
                                            p.SLUONG_2,
                                            p.SLUONG_3,
                                            p.MA_KHANG,
                                            p.NGAY_TAO,
                                            p.NGUOI_TAO,
                                            p.NGAY_SUA,
                                            p.NGUOI_SUA,
                                            p.MA_CNANG
                                        }).ToList();

                var GCS_CHISO_grpA = GCS_CHISO_grpHtai.Union(GCS_CHISO_grpLsu);

                var GCS_CHISO_grpB = from p in GCS_CHISO_grpA
                                     where p.MA_DDO == strMa_ddo
                                     //&& p.MA_CTO == Macto
                                     && p.NGAY_CKY < dtNgaydau
                                     select new
                                     {
                                         p.MA_DVIQLY,
                                         p.ID_CHISO,
                                         p.ID_BCS,
                                         p.MA_DDO,
                                         p.BCS,
                                         p.HS_NHAN,
                                         p.MA_CTO,
                                         p.SO_CTO,
                                         p.KY,
                                         p.THANG,
                                         p.NAM,
                                         p.CHISO_CU,
                                         p.CHISO_MOI,
                                         SAN_LUONG = p.SAN_LUONG == null ? 0 : p.SAN_LUONG,
                                         SLUONG_TTIEP = p.SLUONG_TTIEP == null ? 0 : p.SLUONG_TTIEP,
                                         SLUONG_TRPHU = p.SLUONG_TRPHU == null ? 0 : p.SLUONG_TRPHU,
                                         p.LOAI_CHISO,
                                         p.MA_TTCTO,
                                         p.NGAY_DKY,
                                         p.NGAY_CKY
                                     };

                var GCS_CHISO_grp = from p in GCS_CHISO_grpB  // CMIS2.DB.GCS_CHISO
                                    where p.MA_DDO == strMa_ddo
                                    //&& p.MA_CTO == Macto
                                    && p.NGAY_CKY < dtNgaydau
                                    group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                    select new
                                    {
                                        grp.Key.MA_DDO,
                                        grp.Key.BCS,
                                        grp.Key.KY,
                                        grp.Key.THANG,
                                        grp.Key.NAM,
                                        SAN_LUONG = grp.Sum(c => c.SAN_LUONG) + grp.Sum(c1 => c1.SLUONG_TTIEP) + grp.Sum(c2 => c2.SLUONG_TRPHU)
                                    };

                var kythangnamngay = (from p in gcsCHISO
                                      where p.MA_DDO == strMa_ddo
                                      group p by new { p.KY, p.THANG, p.NAM, p.NGAY_DKY, p.NGAY_CKY } into grp
                                      select new
                                      {
                                          grp.Key.KY,
                                          grp.Key.THANG,
                                          grp.Key.NAM,
                                          grp.Key.NGAY_DKY,
                                          grp.Key.NGAY_CKY
                                      }).ToList();

                var kythangnam = (from p in gcsCHISO
                                  where p.MA_DDO == strMa_ddo
                                  group p by new { p.KY, p.THANG, p.NAM } into grp
                                  orderby grp.Key.NAM, grp.Key.THANG, grp.Key.KY
                                  select new
                                  {
                                      grp.Key.KY,
                                      grp.Key.THANG,
                                      grp.Key.NAM
                                  }).ToList();

                // lấy kỳ tháng năm trước ngày sự cố, bỏ kỳ tháng năm không tròn kỳ -> xem số kỳ còn lại có đủ 3 chu kỳ ?
                DataTable dtKythangnam = new DataTable();
                if (kythangnam != null && kythangnam.Count > 0)
                {
                    dtKythangnam = Utility.LINQToDataTable(kythangnam);

                    if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0) // bỏ kỳ tháng năm  không tròn kỳ trong kỳ tháng năm căn cứ
                    {
                        DataRow[] Arr = dtKythangnam.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());

                        for (int i = 0; i < Arr.Count(); i++)
                        {
                            dtKythangnam.Rows.Remove(Arr[i]);
                        }
                        dtKythangnam.AcceptChanges();
                    }

                }

                var kyEND = from p in kythangnam
                            select new
                            {
                                p.KY,
                                p.THANG,
                                p.NAM,
                                NGAY_DKY = kythangnamngay.Where(c => c.KY == p.KY && c.THANG == p.THANG && c.NAM == p.NAM).Min(c1 => c1.NGAY_DKY),
                                NGAY_CKY = kythangnamngay.Where(d => d.KY == p.KY && d.THANG == p.THANG && d.NAM == p.NAM).Max(c2 => c2.NGAY_CKY)
                            };


                var GCS_CHISO_DC_grp = from p in CMIS2.DB.HDN_CHISO_DC
                                       where p.MA_DDO == strMa_ddo
                                       //&& p.MA_CTO == Macto
                                       group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                       select new
                                       {
                                           grp.Key.MA_DDO,
                                           grp.Key.BCS,
                                           grp.Key.KY,
                                           grp.Key.THANG,
                                           grp.Key.NAM,
                                           SAN_LUONG = grp.Sum(c => c.SAN_LUONG)
                                       };

                //var union = GCS_CHISO_grp.Union(GCS_CHISO_DC_grp);

                var GCS_CHISO_grpJoinkyEND = from p in GCS_CHISO_grp.ToList()
                                             join m in kyEND.ToList()
                                             on new { p.KY, p.THANG, p.NAM }
                                             equals new { m.KY, m.THANG, m.NAM }
                                             select new
                                             {
                                                 p.MA_DDO,
                                                 p.BCS,
                                                 p.KY,
                                                 p.THANG,
                                                 p.NAM,
                                                 p.SAN_LUONG,
                                                 NGAY_DKY = m.NGAY_DKY,
                                                 NGAY_CKY = m.NGAY_CKY
                                             };

                var HDN_DC = from p in CMIS2.DB.HDN_HDONCTIET_DC
                             where p.MA_DDO == strMa_ddo
                             group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                             select new
                             {
                                 grp.Key.MA_DDO,
                                 grp.Key.BCS,
                                 grp.Key.KY,
                                 grp.Key.THANG,
                                 grp.Key.NAM,
                                 SAN_LUONG = grp.Sum(g => g.DIEN_TTHU)
                             };


                var joi1 = from p in GCS_CHISO_grpJoinkyEND.ToList()
                           join m in HDN_DC.ToList()
                           on new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM }
                           equals new { m.MA_DDO, m.BCS, m.KY, m.THANG, m.NAM } into grp
                           from grp1 in grp.DefaultIfEmpty()
                           orderby p.NAM, p.THANG, p.KY, p.BCS
                           select new
                           {
                               p.MA_DDO,
                               p.BCS,
                               p.KY,
                               p.THANG,
                               p.NAM,
                               p.NGAY_DKY,
                               p.NGAY_CKY,
                               SLUONG_END = p.SAN_LUONG + (grp1 == null ? Convert.ToDecimal(0) : Convert.ToDecimal(grp1.SAN_LUONG))
                           };


                var q1 = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c1 => c1.MA_DDO == c.MA_DDO && c1.BCS == c.BCS).Max(c2 => c2.ID_CHISO));

                var gcsCHISO_gannhat = from p in q1
                                       where p.MA_DDO == strMa_ddo
                                       select new
                                       {
                                           p.MA_DVIQLY,
                                           p.ID_CHISO,
                                           p.ID_BCS,
                                           p.MA_DDO,
                                           p.BCS,
                                           p.HS_NHAN,
                                           p.MA_CTO,
                                           p.SO_CTO,
                                           p.KY,
                                           p.THANG,
                                           p.NAM,
                                           p.CHISO_CU,
                                           p.CHISO_MOI,
                                           p.SAN_LUONG,
                                           p.SLUONG_TTIEP,
                                           p.SLUONG_TRPHU,
                                           p.LOAI_CHISO,
                                           p.MA_TTCTO,
                                           p.NGAY_DKY,
                                           p.NGAY_CKY,
                                           p.SLUONG_1,
                                           p.SLUONG_2,
                                           p.SLUONG_3,
                                           p.MA_KHANG,
                                           p.NGAY_TAO,
                                           p.NGUOI_TAO,
                                           p.NGAY_SUA,
                                           p.NGUOI_SUA,
                                           p.MA_CNANG
                                       };


                // lay chung loai
                var HOSOCTO = (from p in CMIS2.DB.DD_HOSO_CTO
                               where p.MA_DVIQLY == strMa_dviqly
                               && p.MA_CTO == Macto
                               select p).ToList();

                string[] ArrMacloai = HOSOCTO.Select(c => c.MA_CLOAI).Distinct().ToArray();
                string Macloaicto = ArrMacloai[0];

                var LOAICTO = from p in CMIS2.DB.D_CLOAI_CTO
                              where p.MA_CLOAI == Macloaicto
                              select new
                              {
                                  p.MA_CLOAI,
                                  p.LOAI_CTO,
                                  p.MO_TA,
                                  p.SO_PHA,
                                  p.SO_DAY,
                                  p.HS_NHAN,
                                  p.SO_CSO,
                                  p.CAP_CXAC,
                                  p.DONG_DIEN,
                                  p.DIEN_AP,
                                  p.VH_CONG,
                                  p.MA_NUOC,
                                  p.MA_HANG,
                                  p.HANGSO_K
                              };

                #endregion

                #region  ======= Lấy sau ngày dừng cấp điện =======

                // dtKythangnam chứa kỳ tháng năm trước ngày sự cố, đã bỏ đi kỳ không tròn kỳ ghi chỉ số
                if (dtKythangnam != null && dtKythangnam.Rows.Count < 3)
                {
                    var gcsCHISOHtaiAfter = (from p in CMIS2.DB.GCS_CHISO
                                        where p.MA_DDO == strMa_ddo
                                        && p.NGAY_DKY >= dtNgaycuoi
                                        select new
                                        {
                                            p.MA_DVIQLY,
                                            p.ID_CHISO,
                                            p.ID_BCS,
                                            p.MA_DDO,
                                            p.BCS,
                                            p.HS_NHAN,
                                            p.MA_CTO,
                                            p.SO_CTO,
                                            p.KY,
                                            p.THANG,
                                            p.NAM,
                                            p.CHISO_CU,
                                            p.CHISO_MOI,
                                            p.SAN_LUONG,
                                            p.SLUONG_TTIEP,
                                            p.SLUONG_TRPHU,
                                            p.LOAI_CHISO,
                                            p.MA_TTCTO,
                                            p.NGAY_DKY,
                                            p.NGAY_CKY,
                                            p.SLUONG_1,
                                            p.SLUONG_2,
                                            p.SLUONG_3,
                                            p.MA_KHANG,
                                            p.NGAY_TAO,
                                            p.NGUOI_TAO,
                                            p.NGAY_SUA,
                                            p.NGUOI_SUA,
                                            p.MA_CNANG
                                        }).ToList();

                    var gcsCHISOLsuAfter = (from p in CMIS2.DB.GCS_CHISO_LSU
                                       where p.MA_DDO == strMa_ddo
                                       && p.NGAY_DKY >= dtNgaycuoi
                                       select new
                                       {
                                           p.MA_DVIQLY,
                                           p.ID_CHISO,
                                           p.ID_BCS,
                                           p.MA_DDO,
                                           p.BCS,
                                           p.HS_NHAN,
                                           p.MA_CTO,
                                           p.SO_CTO,
                                           p.KY,
                                           p.THANG,
                                           p.NAM,
                                           p.CHISO_CU,
                                           p.CHISO_MOI,
                                           p.SAN_LUONG,
                                           p.SLUONG_TTIEP,
                                           p.SLUONG_TRPHU,
                                           p.LOAI_CHISO,
                                           p.MA_TTCTO,
                                           p.NGAY_DKY,
                                           p.NGAY_CKY,
                                           p.SLUONG_1,
                                           p.SLUONG_2,
                                           p.SLUONG_3,
                                           p.MA_KHANG,
                                           p.NGAY_TAO,
                                           p.NGUOI_TAO,
                                           p.NGAY_SUA,
                                           p.NGUOI_SUA,
                                           p.MA_CNANG
                                       }).ToList();

                    var gcsCHISOAfter = gcsCHISOHtaiAfter.Union(gcsCHISOLsuAfter);

                    var GCS_CHISO_grpHtaiAfter = (from p in CMIS2.DB.GCS_CHISO
                                             where p.MA_DDO == strMa_ddo
                                             && p.NGAY_DKY >= dtNgaycuoi
                                             select new
                                             {
                                                 p.MA_DVIQLY,
                                                 p.ID_CHISO,
                                                 p.ID_BCS,
                                                 p.MA_DDO,
                                                 p.BCS,
                                                 p.HS_NHAN,
                                                 p.MA_CTO,
                                                 p.SO_CTO,
                                                 p.KY,
                                                 p.THANG,
                                                 p.NAM,
                                                 p.CHISO_CU,
                                                 p.CHISO_MOI,
                                                 p.SAN_LUONG,
                                                 p.SLUONG_TTIEP,
                                                 p.SLUONG_TRPHU,
                                                 p.LOAI_CHISO,
                                                 p.MA_TTCTO,
                                                 p.NGAY_DKY,
                                                 p.NGAY_CKY,
                                                 p.SLUONG_1,
                                                 p.SLUONG_2,
                                                 p.SLUONG_3,
                                                 p.MA_KHANG,
                                                 p.NGAY_TAO,
                                                 p.NGUOI_TAO,
                                                 p.NGAY_SUA,
                                                 p.NGUOI_SUA,
                                                 p.MA_CNANG
                                             }).ToList();

                    var GCS_CHISO_grpLsuAfter = (from p in CMIS2.DB.GCS_CHISO_LSU
                                            where p.MA_DDO == strMa_ddo
                                            && p.NGAY_DKY >= dtNgaycuoi
                                            select new
                                            {
                                                p.MA_DVIQLY,
                                                p.ID_CHISO,
                                                p.ID_BCS,
                                                p.MA_DDO,
                                                p.BCS,
                                                p.HS_NHAN,
                                                p.MA_CTO,
                                                p.SO_CTO,
                                                p.KY,
                                                p.THANG,
                                                p.NAM,
                                                p.CHISO_CU,
                                                p.CHISO_MOI,
                                                p.SAN_LUONG,
                                                p.SLUONG_TTIEP,
                                                p.SLUONG_TRPHU,
                                                p.LOAI_CHISO,
                                                p.MA_TTCTO,
                                                p.NGAY_DKY,
                                                p.NGAY_CKY,
                                                p.SLUONG_1,
                                                p.SLUONG_2,
                                                p.SLUONG_3,
                                                p.MA_KHANG,
                                                p.NGAY_TAO,
                                                p.NGUOI_TAO,
                                                p.NGAY_SUA,
                                                p.NGUOI_SUA,
                                                p.MA_CNANG
                                            }).ToList();

                    var GCS_CHISO_grpAAfter = GCS_CHISO_grpHtaiAfter.Union(GCS_CHISO_grpLsuAfter);

                    var GCS_CHISO_grpBAfter = from p in GCS_CHISO_grpAAfter
                                         where p.MA_DDO == strMa_ddo
                                         && p.NGAY_DKY >= dtNgaycuoi
                                         select new
                                         {
                                             p.MA_DVIQLY,
                                             p.ID_CHISO,
                                             p.ID_BCS,
                                             p.MA_DDO,
                                             p.BCS,
                                             p.HS_NHAN,
                                             p.MA_CTO,
                                             p.SO_CTO,
                                             p.KY,
                                             p.THANG,
                                             p.NAM,
                                             p.CHISO_CU,
                                             p.CHISO_MOI,
                                             SAN_LUONG = p.SAN_LUONG == null ? 0 : p.SAN_LUONG,
                                             SLUONG_TTIEP = p.SLUONG_TTIEP == null ? 0 : p.SLUONG_TTIEP,
                                             SLUONG_TRPHU = p.SLUONG_TRPHU == null ? 0 : p.SLUONG_TRPHU,
                                             p.LOAI_CHISO,
                                             p.MA_TTCTO,
                                             p.NGAY_DKY,
                                             p.NGAY_CKY
                                         };

                    var GCS_CHISO_grpAfter = from p in GCS_CHISO_grpBAfter  // CMIS2.DB.GCS_CHISO
                                        where p.MA_DDO == strMa_ddo
                                        && p.NGAY_DKY >= dtNgaycuoi
                                        group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                        select new
                                        {
                                            grp.Key.MA_DDO,
                                            grp.Key.BCS,
                                            grp.Key.KY,
                                            grp.Key.THANG,
                                            grp.Key.NAM,
                                            SAN_LUONG = grp.Sum(c => c.SAN_LUONG) + grp.Sum(c1 => c1.SLUONG_TTIEP) + grp.Sum(c2 => c2.SLUONG_TRPHU)
                                        };

                    var kythangnamngayAfter = (from p in gcsCHISOAfter
                                          where p.MA_DDO == strMa_ddo
                                          group p by new { p.KY, p.THANG, p.NAM, p.NGAY_DKY, p.NGAY_CKY } into grp
                                          select new
                                          {
                                              grp.Key.KY,
                                              grp.Key.THANG,
                                              grp.Key.NAM,
                                              grp.Key.NGAY_DKY,
                                              grp.Key.NGAY_CKY
                                          }).ToList();

                    var kythangnamAfter = (from p in gcsCHISOAfter
                                      where p.MA_DDO == strMa_ddo
                                      group p by new { p.KY, p.THANG, p.NAM } into grp
                                      orderby grp.Key.NAM, grp.Key.THANG, grp.Key.KY
                                      select new
                                      {
                                          grp.Key.KY,
                                          grp.Key.THANG,
                                          grp.Key.NAM
                                      }).ToList();

                    var kyENDAfter = from p in kythangnamAfter
                                select new
                                {
                                    p.KY,
                                    p.THANG,
                                    p.NAM,
                                    NGAY_DKY = kythangnamngayAfter.Where(c => c.KY == p.KY && c.THANG == p.THANG && c.NAM == p.NAM).Min(c1 => c1.NGAY_DKY),
                                    NGAY_CKY = kythangnamngayAfter.Where(d => d.KY == p.KY && d.THANG == p.THANG && d.NAM == p.NAM).Max(c2 => c2.NGAY_CKY)
                                };


                    var GCS_CHISO_DC_grpAfter = from p in CMIS2.DB.HDN_CHISO_DC
                                           where p.MA_DDO == strMa_ddo
                                           group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                           select new
                                           {
                                               grp.Key.MA_DDO,
                                               grp.Key.BCS,
                                               grp.Key.KY,
                                               grp.Key.THANG,
                                               grp.Key.NAM,
                                               SAN_LUONG = grp.Sum(c => c.SAN_LUONG)
                                           };

                    //var union = GCS_CHISO_grp.Union(GCS_CHISO_DC_grp);

                    var GCS_CHISO_grpJoinkyENDAfter = from p in GCS_CHISO_grpAfter.ToList()
                                                      join m in kyENDAfter.ToList()
                                                     on new { p.KY, p.THANG, p.NAM }
                                                     equals new { m.KY, m.THANG, m.NAM }
                                                     select new
                                                     {
                                                         p.MA_DDO,
                                                         p.BCS,
                                                         p.KY,
                                                         p.THANG,
                                                         p.NAM,
                                                         p.SAN_LUONG,
                                                         NGAY_DKY = m.NGAY_DKY,
                                                         NGAY_CKY = m.NGAY_CKY
                                                     };

                    var HDN_DCAfter = from p in CMIS2.DB.HDN_HDONCTIET_DC
                                 where p.MA_DDO == strMa_ddo
                                 group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                 select new
                                 {
                                     grp.Key.MA_DDO,
                                     grp.Key.BCS,
                                     grp.Key.KY,
                                     grp.Key.THANG,
                                     grp.Key.NAM,
                                     SAN_LUONG = grp.Sum(g => g.DIEN_TTHU)
                                 };


                    var joi1After = from p in GCS_CHISO_grpJoinkyENDAfter.ToList()
                                    join m in HDN_DCAfter.ToList()
                               on new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM }
                               equals new { m.MA_DDO, m.BCS, m.KY, m.THANG, m.NAM } into grp
                               from grp1 in grp.DefaultIfEmpty()
                               orderby p.NAM, p.THANG, p.KY, p.BCS
                               select new
                               {
                                   p.MA_DDO,
                                   p.BCS,
                                   p.KY,
                                   p.THANG,
                                   p.NAM,
                                   p.NGAY_DKY,
                                   p.NGAY_CKY,
                                   SLUONG_END = p.SAN_LUONG + (grp1 == null ? Convert.ToDecimal(0) : Convert.ToDecimal(grp1.SAN_LUONG))
                               };

                    var joiF = joi1.Union(joi1After);

                    var kythangnamF = kythangnam.Union(kythangnamAfter);

                    if (joi1 != null && joi1.Count() > 0)
                    {
                        DataTable dt = Utility.LINQToDataTable(joi1);
                        dt.TableName = "SAN_LUONG";

                        DataTable dtDDN = new DataTable(); // bỏ đi kỳ gần nhất nếu không tròn kỳ trong sản lượng căn cứ
                        if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0)
                        {
                            DataRow[] Arr = dt.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());
                            dtDDN = Arr.CopyToDataTable();
                            dtDDN.TableName = "DDN";

                            for (int i = 0; i < Arr.Count(); i++)
                            {
                                dt.Rows.Remove(Arr[i]);
                            }
                            dt.AcceptChanges();
                        }

                        DataSet ds = new DataSet();
                        ds.Tables.Add(dt);

                        if (dtDDN != null && dtDDN.Rows.Count > 0)
                        {
                            ds.Tables.Add(dtDDN);
                        }

                        if (joi1After != null && joi1After.Count() > 0)
                        {
                            DataTable dtA = Utility.LINQToDataTable(joi1After);
                            dtA.TableName = "SAN_LUONG_AFTER";
                            ds.Tables.Add(dtA);
                        }

                        if (gcsCHISO_gannhat != null && gcsCHISO_gannhat.Count() > 0)
                        {
                            DataTable dtcs = Utility.LINQToDataTable(gcsCHISO_gannhat);
                            dtcs.TableName = "CHI_SO";
                            ds.Tables.Add(dtcs);
                        }

                        if (kythangnam != null && kythangnam.Count() > 0)
                        {
                            DataTable dtKTN = Utility.LINQToDataTable(kythangnam);
                            dtKTN.TableName = "KYTHANGNAM";

                            if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0) // bỏ kỳ tháng năm  không tròn kỳ trong kỳ tháng năm căn cứ
                            {
                                DataRow[] Arr = dtKTN.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());

                                for (int i = 0; i < Arr.Count(); i++)
                                {
                                    dtKTN.Rows.Remove(Arr[i]);
                                }
                                dtKTN.AcceptChanges();
                            }

                            ds.Tables.Add(dtKTN);
                        }

                        if (kythangnamAfter != null && kythangnamAfter.Count() > 0)
                        {
                            DataTable dtKTNAf = Utility.LINQToDataTable(kythangnamAfter);
                            dtKTNAf.TableName = "KYTHANGNAM_AFTER";
                            ds.Tables.Add(dtKTNAf);
                        }

                        if (LOAICTO != null && LOAICTO.Count() > 0)
                        {
                            DataTable dtLCTO = Utility.LINQToDataTable(LOAICTO);
                            dtLCTO.TableName = "LOAICTO";
                            ds.Tables.Add(dtLCTO);
                        }

                        return ds;
                    }

                }

                #endregion


                if (joi1 != null && joi1.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(joi1);
                    dt.TableName = "SAN_LUONG";

                    DataTable dtDDN = new DataTable(); // bỏ đi kỳ gần nhất nếu không tròn kỳ trong sản lượng căn cứ
                    if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0)
                    {
                        DataRow[] Arr = dt.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());
                        dtDDN = Arr.CopyToDataTable();
                        dtDDN.TableName = "DDN";

                        for (int i = 0; i < Arr.Count(); i++)
                        {
                            dt.Rows.Remove(Arr[i]);
                        }
                        dt.AcceptChanges();
                    }

                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);

                    if (dtDDN != null && dtDDN.Rows.Count > 0)
                    {
                        ds.Tables.Add(dtDDN);
                    }


                    if (gcsCHISO_gannhat != null && gcsCHISO_gannhat.Count() > 0)
                    {
                        DataTable dtcs = Utility.LINQToDataTable(gcsCHISO_gannhat);
                        dtcs.TableName = "CHI_SO";
                        ds.Tables.Add(dtcs);
                    }

                    if (kythangnam != null && kythangnam.Count() > 0)
                    {
                        DataTable dtKTN = Utility.LINQToDataTable(kythangnam);
                        dtKTN.TableName = "KYTHANGNAM";

                        if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0) // bỏ kỳ tháng năm  không tròn kỳ trong kỳ tháng năm căn cứ
                        {
                            DataRow[] Arr = dtKTN.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());

                            for (int i = 0; i < Arr.Count(); i++)
                            {
                                dtKTN.Rows.Remove(Arr[i]);
                            }
                            dtKTN.AcceptChanges();
                        }

                        ds.Tables.Add(dtKTN);
                    }

                    if (LOAICTO != null && LOAICTO.Count() > 0)
                    {
                        DataTable dtLCTO = Utility.LINQToDataTable(LOAICTO);
                        dtLCTO.TableName = "LOAICTO";
                        ds.Tables.Add(dtLCTO);
                    }

                    return ds;
                }

                #endregion

                return null;

            }
            catch
            { return null; }

        }

        public DataSet Get_Sanluong_Cancu_Co_After(string strMa_ddo, DateTime dtNgaydau, DateTime dtNgaycuoi)
        {
            try
            {

                #region Lấy theo GCS_CHISO

                #region Hien tai
                var gcsCHISOLsu = (from p in CMIS2.DB.GCS_CHISO_LSU
                                   where p.MA_DDO == strMa_ddo
                                   && p.BCS == "KT"
                                   && p.NGAY_CKY < dtNgaydau
                                   select new
                                   {
                                       p.MA_DVIQLY,
                                       p.ID_CHISO,
                                       p.ID_BCS,
                                       p.MA_DDO,
                                       p.BCS,
                                       p.HS_NHAN,
                                       p.MA_CTO,
                                       p.SO_CTO,
                                       p.KY,
                                       p.THANG,
                                       p.NAM,
                                       p.CHISO_CU,
                                       p.CHISO_MOI,
                                       p.SAN_LUONG,
                                       p.SLUONG_TTIEP,
                                       p.SLUONG_TRPHU,
                                       p.LOAI_CHISO,
                                       p.MA_TTCTO,
                                       p.NGAY_DKY,
                                       p.NGAY_CKY,
                                       p.SLUONG_1,
                                       p.SLUONG_2,
                                       p.SLUONG_3,
                                       p.MA_KHANG,
                                       p.NGAY_TAO,
                                       p.NGUOI_TAO,
                                       p.NGAY_SUA,
                                       p.NGUOI_SUA,
                                       p.MA_CNANG
                                   }).ToList();

                var gcsCHISOHtai = (from p in CMIS2.DB.GCS_CHISO
                                    where p.MA_DDO == strMa_ddo
                                    && p.BCS == "KT"
                                    && p.NGAY_CKY < dtNgaydau
                                    select new
                                    {
                                        p.MA_DVIQLY,
                                        p.ID_CHISO,
                                        p.ID_BCS,
                                        p.MA_DDO,
                                        p.BCS,
                                        p.HS_NHAN,
                                        p.MA_CTO,
                                        p.SO_CTO,
                                        p.KY,
                                        p.THANG,
                                        p.NAM,
                                        p.CHISO_CU,
                                        p.CHISO_MOI,
                                        p.SAN_LUONG,
                                        p.SLUONG_TTIEP,
                                        p.SLUONG_TRPHU,
                                        p.LOAI_CHISO,
                                        p.MA_TTCTO,
                                        p.NGAY_DKY,
                                        p.NGAY_CKY,
                                        p.SLUONG_1,
                                        p.SLUONG_2,
                                        p.SLUONG_3,
                                        p.MA_KHANG,
                                        p.NGAY_TAO,
                                        p.NGUOI_TAO,
                                        p.NGAY_SUA,
                                        p.NGUOI_SUA,
                                        p.MA_CNANG
                                    }).ToList();

                var gcsCHISO = gcsCHISOHtai.Union(gcsCHISOLsu);

                var gcsCHISOMaxID = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c2 => c2.MA_DDO == c.MA_DDO).Max(c3 => c3.ID_CHISO));

                DataTable dtkhacDDK = new DataTable(); // luu Ky gan nhat trong truong hop ky nay co treo thao và chưa tròn kỳ - chưa ra hóa đơn.
                if (gcsCHISOMaxID != null && gcsCHISOMaxID.Count() > 0)
                {
                    DataTable dt1 = Utility.LINQToDataTable(gcsCHISOMaxID);

                    if (dt1.Rows[0]["LOAI_CHISO"].ToString() != "DDK")
                    {
                        dtkhacDDK = dt1.Copy();
                    }

                }

                string[] ArrMacto = gcsCHISOMaxID.Select(c => c.MA_CTO).Distinct().ToArray();
                string Macto = ArrMacto[0];

                var GCS_CHISO_grpHtai = (from p in CMIS2.DB.GCS_CHISO
                                         where p.MA_DDO == strMa_ddo
                                         && p.BCS == "KT" // && p.MA_CTO == Macto
                                         && p.NGAY_CKY < dtNgaydau
                                         select new
                                         {
                                             p.MA_DVIQLY,
                                             p.ID_CHISO,
                                             p.ID_BCS,
                                             p.MA_DDO,
                                             p.BCS,
                                             p.HS_NHAN,
                                             p.MA_CTO,
                                             p.SO_CTO,
                                             p.KY,
                                             p.THANG,
                                             p.NAM,
                                             p.CHISO_CU,
                                             p.CHISO_MOI,
                                             p.SAN_LUONG,
                                             p.SLUONG_TTIEP,
                                             p.SLUONG_TRPHU,
                                             p.LOAI_CHISO,
                                             p.MA_TTCTO,
                                             p.NGAY_DKY,
                                             p.NGAY_CKY,
                                             p.SLUONG_1,
                                             p.SLUONG_2,
                                             p.SLUONG_3,
                                             p.MA_KHANG,
                                             p.NGAY_TAO,
                                             p.NGUOI_TAO,
                                             p.NGAY_SUA,
                                             p.NGUOI_SUA,
                                             p.MA_CNANG
                                         }).ToList();

                var GCS_CHISO_grpLsu = (from p in CMIS2.DB.GCS_CHISO_LSU
                                        where p.MA_DDO == strMa_ddo
                                        && p.BCS == "KT" // && p.MA_CTO == Macto
                                        && p.NGAY_CKY < dtNgaydau
                                        select new
                                        {
                                            p.MA_DVIQLY,
                                            p.ID_CHISO,
                                            p.ID_BCS,
                                            p.MA_DDO,
                                            p.BCS,
                                            p.HS_NHAN,
                                            p.MA_CTO,
                                            p.SO_CTO,
                                            p.KY,
                                            p.THANG,
                                            p.NAM,
                                            p.CHISO_CU,
                                            p.CHISO_MOI,
                                            p.SAN_LUONG,
                                            p.SLUONG_TTIEP,
                                            p.SLUONG_TRPHU,
                                            p.LOAI_CHISO,
                                            p.MA_TTCTO,
                                            p.NGAY_DKY,
                                            p.NGAY_CKY,
                                            p.SLUONG_1,
                                            p.SLUONG_2,
                                            p.SLUONG_3,
                                            p.MA_KHANG,
                                            p.NGAY_TAO,
                                            p.NGUOI_TAO,
                                            p.NGAY_SUA,
                                            p.NGUOI_SUA,
                                            p.MA_CNANG
                                        }).ToList();

                var GCS_CHISO_grpA = GCS_CHISO_grpHtai.Union(GCS_CHISO_grpLsu);

                var GCS_CHISO_grpB = from p in GCS_CHISO_grpA
                                     where p.MA_DDO == strMa_ddo
                                     && p.BCS == "KT"
                                     && p.NGAY_CKY < dtNgaydau
                                     select new
                                     {
                                         p.MA_DVIQLY,
                                         p.ID_CHISO,
                                         p.ID_BCS,
                                         p.MA_DDO,
                                         p.BCS,
                                         p.HS_NHAN,
                                         p.MA_CTO,
                                         p.SO_CTO,
                                         p.KY,
                                         p.THANG,
                                         p.NAM,
                                         p.CHISO_CU,
                                         p.CHISO_MOI,
                                         SAN_LUONG = p.SAN_LUONG == null ? 0 : p.SAN_LUONG,
                                         SLUONG_TTIEP = p.SLUONG_TTIEP == null ? 0 : p.SLUONG_TTIEP,
                                         SLUONG_TRPHU = p.SLUONG_TRPHU == null ? 0 : p.SLUONG_TRPHU,
                                         p.LOAI_CHISO,
                                         p.MA_TTCTO,
                                         p.NGAY_DKY,
                                         p.NGAY_CKY
                                     };


                var GCS_CHISO_grp = from p in GCS_CHISO_grpB // CMIS2.DB.GCS_CHISO
                                    where p.MA_DDO == strMa_ddo
                                    && p.BCS == "KT" // && p.MA_CTO == Macto
                                    && p.NGAY_CKY < dtNgaydau
                                    group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                    select new
                                    {
                                        grp.Key.MA_DDO,
                                        grp.Key.BCS,
                                        grp.Key.KY,
                                        grp.Key.THANG,
                                        grp.Key.NAM,
                                        SAN_LUONG = grp.Sum(c => c.SAN_LUONG) + grp.Sum(c1 => c1.SLUONG_TTIEP) + grp.Sum(c2 => c2.SLUONG_TRPHU)
                                    };

                var kythangnamngay = (from p in gcsCHISO
                                      where p.MA_DDO == strMa_ddo
                                      group p by new { p.KY, p.THANG, p.NAM, p.NGAY_DKY, p.NGAY_CKY } into grp
                                      select new
                                      {
                                          grp.Key.KY,
                                          grp.Key.THANG,
                                          grp.Key.NAM,
                                          grp.Key.NGAY_DKY,
                                          grp.Key.NGAY_CKY
                                      }).ToList();

                var kythangnam = (from p in gcsCHISO
                                  where p.MA_DDO == strMa_ddo
                                  group p by new { p.KY, p.THANG, p.NAM } into grp
                                  select new
                                  {
                                      grp.Key.KY,
                                      grp.Key.THANG,
                                      grp.Key.NAM
                                  }).ToList();

                var kyEND = from p in kythangnam
                            select new
                            {
                                p.KY,
                                p.THANG,
                                p.NAM,
                                NGAY_DKY = kythangnamngay.Where(c => c.KY == p.KY && c.THANG == p.THANG && c.NAM == p.NAM).Min(c1 => c1.NGAY_DKY),
                                NGAY_CKY = kythangnamngay.Where(d => d.KY == p.KY && d.THANG == p.THANG && d.NAM == p.NAM).Max(c2 => c2.NGAY_CKY)
                            };

                var GCS_CHISO_DC_grp = from p in CMIS2.DB.HDN_CHISO_DC
                                       where p.MA_DDO == strMa_ddo
                                       && p.BCS == "KT"
                                       group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                       select new
                                       {
                                           grp.Key.MA_DDO,
                                           grp.Key.BCS,
                                           grp.Key.KY,
                                           grp.Key.THANG,
                                           grp.Key.NAM,
                                           SAN_LUONG = grp.Sum(c => c.SAN_LUONG)
                                       };

                //var union = GCS_CHISO_grp.Union(GCS_CHISO_DC_grp);

                var GCS_CHISO_grpJoinkyEND = from p in GCS_CHISO_grp.ToList()
                                             join m in kyEND.ToList()
                                             on new { p.KY, p.THANG, p.NAM }
                                             equals new { m.KY, m.THANG, m.NAM }
                                             select new
                                             {
                                                 p.MA_DDO,
                                                 p.BCS,
                                                 p.KY,
                                                 p.THANG,
                                                 p.NAM,
                                                 p.SAN_LUONG,
                                                 NGAY_DKY = m.NGAY_DKY,
                                                 NGAY_CKY = m.NGAY_CKY
                                             };

                var HDN_DC = from p in CMIS2.DB.HDN_HDONCTIET_DC
                             where p.MA_DDO == strMa_ddo
                             group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                             select new
                             {
                                 grp.Key.MA_DDO,
                                 grp.Key.BCS,
                                 grp.Key.KY,
                                 grp.Key.THANG,
                                 grp.Key.NAM,
                                 SAN_LUONG = grp.Sum(g => g.DIEN_TTHU)
                             };


                var joi1 = from p in GCS_CHISO_grpJoinkyEND.ToList()
                           join m in HDN_DC.ToList()
                           on new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM }
                           equals new { m.MA_DDO, m.BCS, m.KY, m.THANG, m.NAM } into grp
                           from grp1 in grp.DefaultIfEmpty()
                           orderby p.NAM, p.THANG, p.KY, p.BCS
                           select new
                           {
                               p.MA_DDO,
                               p.BCS,
                               p.KY,
                               p.THANG,
                               p.NAM,
                               p.NGAY_DKY,
                               p.NGAY_CKY,
                               SLUONG_END = p.SAN_LUONG + (grp1 == null ? Convert.ToDecimal(0) : Convert.ToDecimal(grp1.SAN_LUONG))
                           };


                var gcsCHISO_gannhat = gcsCHISO.Where(c => c.ID_CHISO == gcsCHISO.Where(c1 => c1.MA_DDO == c.MA_DDO).Max(c2 => c2.ID_CHISO));

                #endregion

                #region === Lấy sau ngày sự cố ===

                if (kythangnam != null && kythangnam.Count() < 3)
                {
                    var gcsCHISOLsuAfter = (from p in CMIS2.DB.GCS_CHISO_LSU
                                       where p.MA_DDO == strMa_ddo
                                       && p.BCS == "KT"
                                       && p.NGAY_DKY >= dtNgaycuoi
                                       select new
                                       {
                                           p.MA_DVIQLY,
                                           p.ID_CHISO,
                                           p.ID_BCS,
                                           p.MA_DDO,
                                           p.BCS,
                                           p.HS_NHAN,
                                           p.MA_CTO,
                                           p.SO_CTO,
                                           p.KY,
                                           p.THANG,
                                           p.NAM,
                                           p.CHISO_CU,
                                           p.CHISO_MOI,
                                           p.SAN_LUONG,
                                           p.SLUONG_TTIEP,
                                           p.SLUONG_TRPHU,
                                           p.LOAI_CHISO,
                                           p.MA_TTCTO,
                                           p.NGAY_DKY,
                                           p.NGAY_CKY,
                                           p.SLUONG_1,
                                           p.SLUONG_2,
                                           p.SLUONG_3,
                                           p.MA_KHANG,
                                           p.NGAY_TAO,
                                           p.NGUOI_TAO,
                                           p.NGAY_SUA,
                                           p.NGUOI_SUA,
                                           p.MA_CNANG
                                       }).ToList();

                    var gcsCHISOHtaiAfter = (from p in CMIS2.DB.GCS_CHISO
                                        where p.MA_DDO == strMa_ddo
                                        && p.BCS == "KT"
                                        && p.NGAY_DKY >= dtNgaycuoi
                                        select new
                                        {
                                            p.MA_DVIQLY,
                                            p.ID_CHISO,
                                            p.ID_BCS,
                                            p.MA_DDO,
                                            p.BCS,
                                            p.HS_NHAN,
                                            p.MA_CTO,
                                            p.SO_CTO,
                                            p.KY,
                                            p.THANG,
                                            p.NAM,
                                            p.CHISO_CU,
                                            p.CHISO_MOI,
                                            p.SAN_LUONG,
                                            p.SLUONG_TTIEP,
                                            p.SLUONG_TRPHU,
                                            p.LOAI_CHISO,
                                            p.MA_TTCTO,
                                            p.NGAY_DKY,
                                            p.NGAY_CKY,
                                            p.SLUONG_1,
                                            p.SLUONG_2,
                                            p.SLUONG_3,
                                            p.MA_KHANG,
                                            p.NGAY_TAO,
                                            p.NGUOI_TAO,
                                            p.NGAY_SUA,
                                            p.NGUOI_SUA,
                                            p.MA_CNANG
                                        }).ToList();

                    var gcsCHISOAfter = gcsCHISOHtaiAfter.Union(gcsCHISOLsuAfter);

                    var GCS_CHISO_grpHtaiAfter = (from p in CMIS2.DB.GCS_CHISO
                                             where p.MA_DDO == strMa_ddo
                                             && p.BCS == "KT" // && p.MA_CTO == Macto
                                             && p.NGAY_DKY >= dtNgaycuoi
                                             select new
                                             {
                                                 p.MA_DVIQLY,
                                                 p.ID_CHISO,
                                                 p.ID_BCS,
                                                 p.MA_DDO,
                                                 p.BCS,
                                                 p.HS_NHAN,
                                                 p.MA_CTO,
                                                 p.SO_CTO,
                                                 p.KY,
                                                 p.THANG,
                                                 p.NAM,
                                                 p.CHISO_CU,
                                                 p.CHISO_MOI,
                                                 p.SAN_LUONG,
                                                 p.SLUONG_TTIEP,
                                                 p.SLUONG_TRPHU,
                                                 p.LOAI_CHISO,
                                                 p.MA_TTCTO,
                                                 p.NGAY_DKY,
                                                 p.NGAY_CKY,
                                                 p.SLUONG_1,
                                                 p.SLUONG_2,
                                                 p.SLUONG_3,
                                                 p.MA_KHANG,
                                                 p.NGAY_TAO,
                                                 p.NGUOI_TAO,
                                                 p.NGAY_SUA,
                                                 p.NGUOI_SUA,
                                                 p.MA_CNANG
                                             }).ToList();

                    var GCS_CHISO_grpLsuAfter = (from p in CMIS2.DB.GCS_CHISO_LSU
                                            where p.MA_DDO == strMa_ddo
                                            && p.BCS == "KT" // && p.MA_CTO == Macto
                                            && p.NGAY_DKY >= dtNgaycuoi
                                            select new
                                            {
                                                p.MA_DVIQLY,
                                                p.ID_CHISO,
                                                p.ID_BCS,
                                                p.MA_DDO,
                                                p.BCS,
                                                p.HS_NHAN,
                                                p.MA_CTO,
                                                p.SO_CTO,
                                                p.KY,
                                                p.THANG,
                                                p.NAM,
                                                p.CHISO_CU,
                                                p.CHISO_MOI,
                                                p.SAN_LUONG,
                                                p.SLUONG_TTIEP,
                                                p.SLUONG_TRPHU,
                                                p.LOAI_CHISO,
                                                p.MA_TTCTO,
                                                p.NGAY_DKY,
                                                p.NGAY_CKY,
                                                p.SLUONG_1,
                                                p.SLUONG_2,
                                                p.SLUONG_3,
                                                p.MA_KHANG,
                                                p.NGAY_TAO,
                                                p.NGUOI_TAO,
                                                p.NGAY_SUA,
                                                p.NGUOI_SUA,
                                                p.MA_CNANG
                                            }).ToList();

                    var GCS_CHISO_grpAAfter = GCS_CHISO_grpHtaiAfter.Union(GCS_CHISO_grpLsuAfter);

                    var GCS_CHISO_grpBAfter = from p in GCS_CHISO_grpAAfter
                                         where p.MA_DDO == strMa_ddo
                                         && p.BCS == "KT"
                                         && p.NGAY_DKY >= dtNgaycuoi
                                         select new
                                         {
                                             p.MA_DVIQLY,
                                             p.ID_CHISO,
                                             p.ID_BCS,
                                             p.MA_DDO,
                                             p.BCS,
                                             p.HS_NHAN,
                                             p.MA_CTO,
                                             p.SO_CTO,
                                             p.KY,
                                             p.THANG,
                                             p.NAM,
                                             p.CHISO_CU,
                                             p.CHISO_MOI,
                                             SAN_LUONG = p.SAN_LUONG == null ? 0 : p.SAN_LUONG,
                                             SLUONG_TTIEP = p.SLUONG_TTIEP == null ? 0 : p.SLUONG_TTIEP,
                                             SLUONG_TRPHU = p.SLUONG_TRPHU == null ? 0 : p.SLUONG_TRPHU,
                                             p.LOAI_CHISO,
                                             p.MA_TTCTO,
                                             p.NGAY_DKY,
                                             p.NGAY_CKY
                                         };


                    var GCS_CHISO_grpAfter = from p in GCS_CHISO_grpBAfter // CMIS2.DB.GCS_CHISO
                                        where p.MA_DDO == strMa_ddo
                                        && p.BCS == "KT" // && p.MA_CTO == Macto
                                        && p.NGAY_DKY >= dtNgaycuoi
                                        group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                        select new
                                        {
                                            grp.Key.MA_DDO,
                                            grp.Key.BCS,
                                            grp.Key.KY,
                                            grp.Key.THANG,
                                            grp.Key.NAM,
                                            SAN_LUONG = grp.Sum(c => c.SAN_LUONG) + grp.Sum(c1 => c1.SLUONG_TTIEP) + grp.Sum(c2 => c2.SLUONG_TRPHU)
                                        };

                    var kythangnamngayAfter = (from p in gcsCHISOAfter
                                          where p.MA_DDO == strMa_ddo
                                          group p by new { p.KY, p.THANG, p.NAM, p.NGAY_DKY, p.NGAY_CKY } into grp
                                          select new
                                          {
                                              grp.Key.KY,
                                              grp.Key.THANG,
                                              grp.Key.NAM,
                                              grp.Key.NGAY_DKY,
                                              grp.Key.NGAY_CKY
                                          }).ToList();

                    var kythangnamAfter = (from p in gcsCHISOAfter
                                      where p.MA_DDO == strMa_ddo
                                      group p by new { p.KY, p.THANG, p.NAM } into grp
                                      select new
                                      {
                                          grp.Key.KY,
                                          grp.Key.THANG,
                                          grp.Key.NAM
                                      }).ToList();

                    var kyENDAfter = from p in kythangnamAfter
                                select new
                                {
                                    p.KY,
                                    p.THANG,
                                    p.NAM,
                                    NGAY_DKY = kythangnamngayAfter.Where(c => c.KY == p.KY && c.THANG == p.THANG && c.NAM == p.NAM).Min(c1 => c1.NGAY_DKY),
                                    NGAY_CKY = kythangnamngayAfter.Where(d => d.KY == p.KY && d.THANG == p.THANG && d.NAM == p.NAM).Max(c2 => c2.NGAY_CKY)
                                };

                    var GCS_CHISO_DC_grpAfter = from p in CMIS2.DB.HDN_CHISO_DC
                                           where p.MA_DDO == strMa_ddo
                                           && p.BCS == "KT"
                                           group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                           select new
                                           {
                                               grp.Key.MA_DDO,
                                               grp.Key.BCS,
                                               grp.Key.KY,
                                               grp.Key.THANG,
                                               grp.Key.NAM,
                                               SAN_LUONG = grp.Sum(c => c.SAN_LUONG)
                                           };

                    //var union = GCS_CHISO_grp.Union(GCS_CHISO_DC_grp);

                    var GCS_CHISO_grpJoinkyENDAfter = from p in GCS_CHISO_grpAfter.ToList()
                                                      join m in kyENDAfter.ToList()
                                                     on new { p.KY, p.THANG, p.NAM }
                                                     equals new { m.KY, m.THANG, m.NAM }
                                                     select new
                                                     {
                                                         p.MA_DDO,
                                                         p.BCS,
                                                         p.KY,
                                                         p.THANG,
                                                         p.NAM,
                                                         p.SAN_LUONG,
                                                         NGAY_DKY = m.NGAY_DKY,
                                                         NGAY_CKY = m.NGAY_CKY
                                                     };

                    var HDN_DCAfter = from p in CMIS2.DB.HDN_HDONCTIET_DC
                                     where p.MA_DDO == strMa_ddo
                                     group p by new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM } into grp
                                     select new
                                     {
                                         grp.Key.MA_DDO,
                                         grp.Key.BCS,
                                         grp.Key.KY,
                                         grp.Key.THANG,
                                         grp.Key.NAM,
                                         SAN_LUONG = grp.Sum(g => g.DIEN_TTHU)
                                     };

                    var joi1After = from p in GCS_CHISO_grpJoinkyENDAfter.ToList()
                               join m in HDN_DCAfter.ToList()
                               on new { p.MA_DDO, p.BCS, p.KY, p.THANG, p.NAM }
                               equals new { m.MA_DDO, m.BCS, m.KY, m.THANG, m.NAM } into grp
                               from grp1 in grp.DefaultIfEmpty()
                               orderby p.NAM, p.THANG, p.KY, p.BCS
                               select new
                               {
                                   p.MA_DDO,
                                   p.BCS,
                                   p.KY,
                                   p.THANG,
                                   p.NAM,
                                   p.NGAY_DKY,
                                   p.NGAY_CKY,
                                   SLUONG_END = p.SAN_LUONG + (grp1 == null ? Convert.ToDecimal(0) : Convert.ToDecimal(grp1.SAN_LUONG))
                               };

                    var joiF = joi1.Union(joi1After);

                    var kythangnamF = kythangnam.Union(kythangnamAfter);

                    if (joi1 != null && joi1.Count() > 0)
                    {
                        DataTable dt = Utility.LINQToDataTable(joi1);
                        dt.TableName = "SAN_LUONG";

                        DataTable dtDDN = new DataTable();
                        if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0)
                        {
                            DataRow[] Arr = dt.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());
                            dtDDN = Arr.CopyToDataTable();
                            dtDDN.TableName = "DDN";

                            for (int i = 0; i < Arr.Count(); i++)
                            {
                                dt.Rows.Remove(Arr[i]);
                            }
                            dt.AcceptChanges();
                        }

                        DataSet ds = new DataSet();
                        ds.Tables.Add(dt);

                        if (dtDDN != null && dtDDN.Rows.Count > 0)
                        {
                            ds.Tables.Add(dtDDN);
                        }

                        if (joi1After != null && joi1After.Count() > 0)
                        {
                            DataTable dtA = Utility.LINQToDataTable(joi1After);
                            dtA.TableName = "SAN_LUONG_AFTER";
                            ds.Tables.Add(dtA);
                        }

                        if (gcsCHISO_gannhat != null && gcsCHISO_gannhat.Count() > 0)
                        {
                            DataTable dtcs = Utility.LINQToDataTable(gcsCHISO_gannhat);
                            dtcs.TableName = "CHI_SO";
                            ds.Tables.Add(dtcs);
                        }
                        return ds;
                    }


                }

                #endregion


                if (joi1 != null && joi1.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(joi1);
                    dt.TableName = "SAN_LUONG";

                    DataTable dtDDN = new DataTable();
                    if (dtkhacDDK != null && dtkhacDDK.Rows.Count > 0)
                    {
                        DataRow[] Arr = dt.Select("KY = " + dtkhacDDK.Rows[0]["KY"].ToString() + " AND THANG = " + dtkhacDDK.Rows[0]["THANG"].ToString() + " AND NAM = " + dtkhacDDK.Rows[0]["NAM"].ToString());
                        dtDDN = Arr.CopyToDataTable();
                        dtDDN.TableName = "DDN";

                        for (int i = 0; i < Arr.Count(); i++)
                        {
                            dt.Rows.Remove(Arr[i]);
                        }
                        dt.AcceptChanges();
                    }

                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);

                    if (dtDDN != null && dtDDN.Rows.Count > 0)
                    {
                        ds.Tables.Add(dtDDN);
                    }

                    if (gcsCHISO_gannhat != null && gcsCHISO_gannhat.Count() > 0)
                    {
                        DataTable dtcs = Utility.LINQToDataTable(gcsCHISO_gannhat);
                        dtcs.TableName = "CHI_SO";
                        ds.Tables.Add(dtcs);
                    }
                    return ds;
                }

                #endregion


                return null;

            }
            catch
            { return null; }

        }

        public DataSet Get_ky_GCS(string strMa_dviqly, string strMa_ddo)
        {
            try
            {
                var DDOSOGCS = (from  p in CMIS2.DB.HDG_DDO_SOGCS
                               where p.MA_DDO == strMa_ddo
                               select p).ToList();

                var DDOSOGCSMaxHL = DDOSOGCS.Where(c=> c.NGAY_HLUC == DDOSOGCS.Where(c2=> c2.MA_DDO == c.MA_DDO).Max(c3=> c3.NGAY_HLUC));

                string[] ArrSogcs = DDOSOGCSMaxHL.Select(c=> c.MA_SOGCS).Distinct().ToArray();

                string Masogcs = ArrSogcs[0];

                var SOGCS = from p in CMIS2.DB.D_SOGCS
                            where p.MA_DVIQLY == strMa_dviqly
                            && p.MA_SOGCS == Masogcs
                            select new 
                            {
                                p.MA_DVIQLY,
                                p.MA_SOGCS,
                                p.TEN_SOGCS,
                                p.SO_KY,
                                p.TINH_TRANG,
                                p.LOAI_SOGCS,
                                p.NGAY_GHI,
                                p.MA_TO,
                                p.NGAY_HLUC
                            };

                if (SOGCS != null && SOGCS.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(SOGCS);
                    dt.TableName = "SOGCS";
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }

                return null;


            }
            catch
            { return null; }
        }

        public DataSet Get_ky_hdon_gannhat(string strMa_dviqly, string strMa_ddo)
        {
            try
            {
                var HDON_CTIET = (from p in CMIS2.DB.HDN_HDONCTIET
                                 where p.MA_DVIQLY == strMa_dviqly
                                 && p.MA_DDO == strMa_ddo
                                 select new 
                                 {
                                     p.MA_DVIQLY,
                                     p.ID_HDONCTIET,
                                     p.ID_HDON,
                                     p.MA_KHANG,
                                     p.MA_DDO,
                                     p.MA_SOGCS,
                                     p.KY,
                                     p.THANG,
                                     p.NAM
                                 }).ToList();

                var HDON_CTIETMaxID = HDON_CTIET.Where(c => c.ID_HDONCTIET == HDON_CTIET.Where(c2 => c2.MA_DDO == c.MA_DDO).Max(c3 => c3.ID_HDONCTIET));

                if (HDON_CTIETMaxID != null && HDON_CTIETMaxID.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(HDON_CTIETMaxID);
                    dt.TableName = "HDON";
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }

                return null;

            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_D_COSFI(decimal hsCOSFI)
        {
            try
            {
                var q = from p in CMIS2.DB.D_COSFI
                        where p.HS_COSFI == hsCOSFI
                        select new 
                        {
                            p.HS_COSFI,
                            p.KCOSFI,
                            p.NGAY_ADUNG,
                            p.NGAY_TAO,
                            p.NGUOI_TAO,
                            p.NGAY_SUA,
                            p.NGUOI_SUA,
                            p.MA_CNANG
                        };

                if (q != null && q.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(q);
                    dt.TableName = "D_COSFI";
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }

                return null;
            }
            catch
            {
                return null;
            }
        }

        public DataSet Get_DDO_by_MaKHANG(string  strMa_dviqly, string strMa_khang)
        {
            try
            {
                var q = (from p in CMIS2.DB.HDG_DIEM_DO
                        where p.MA_DVIQLY == strMa_dviqly
                        && p.MA_KHANG == strMa_khang
                        select p).ToList();

                var m = q.Where(c => c.NGAY_HLUC == q.Where(c1 => c1.MA_DDO == c.MA_DDO).Max(c2 => c2.NGAY_HLUC));

                var grpDDO = from p in m
                          where p.MA_KHANG == strMa_khang
                          group p by new { p.MA_DVIQLY, p.MA_DDO, p.ID_HDONG, p.MA_KHANG, p.MA_CAPDA, p.DIA_CHI, p.SO_PHA, p.MO_TA } into grp
                          select new
                          {
                              grp.Key.MA_DVIQLY,
                              grp.Key.MA_DDO,
                              grp.Key.ID_HDONG,
                              grp.Key.MA_KHANG,
                              grp.Key.MA_CAPDA,
                              grp.Key.DIA_CHI,
                              grp.Key.SO_PHA,
                              grp.Key.MO_TA
                          };

                if (grpDDO != null && grpDDO.Count() > 0)
                {
                    DataTable dt = Utility.LINQToDataTable(grpDDO);
                    dt.TableName = "DIEM_DO";
                    dt.AcceptChanges();
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }
                return null;

            }
            catch
            {
                return null;
            }
 
        }

        //public DateTime Get_chiso_ky_gannhat(string strMa_ddo, int intThangHD, int intNamHD)
        //{ }
    }
}
